Skip to Content
0

need help creating a variable in web intelligence which counts edits in a form

Nov 04, 2016 at 03:44 PM

59

avatar image

a scorecard is a form where the supervisor goes and scores the employee which are called ipas while the IPA is doing an encounter, encounter is where the IPA is helping a customer and every time he is done helping a customer a unique ID is created which is called encounter ID and the scorecard has lots of categories that the Supervisor scores on like compliance, accuracy, completeness, the problem is there is no object that keeps track of all the individuals that are changed if the supervisor goes back to the scorecard to change something after submitting it but they do have a object which keeps count every time the whole scorecard is updated the scorecard id changes as well, for example if the original ID is 122058 the new ID will be 122059, the key identifier is the encounter ID which has same ID as the original scorecard and they also have a object which shows the previous ID for the original scorecard that was edited. If compliance was updated in a scorecard from yes to no that would be 1 count.

here is a case statement that I created in SQL Server which is not showing the correct update.

case when min([PreviousID]) over ( order by [Scorecard_ID]) is null and max([PreviousID])over ( order by [Scorecard_ID]) is not null and LAG([Compliance1]) over ( order by [Scorecard_ID]) <> LEAD ([Compliance1]) over ( order by [Scorecard_ID]) then '1' else '0'

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Mahboob Mohammed Nov 04, 2016 at 04:12 PM
0

Hi Ahmad,

It's difficult to visualize due to the complexity of your scenario. Would you please add some sample data and screenshots to make it clear? The screenshot and sample data should include, what you currently see, what you want to see, and anything that you know is important.

Mahboob Mohammed

Show 2 Share
10 |10000 characters needed characters left characters exceeded
edit1.pngedit2.pngedit3.png

Hi Mehboob,
I have attached 3 screenshots, first one is the mock up how the report is suppose to look like, second is what I'm seeing in dw where the edit is showing 0 but its suppose to show 1 as you can see the update_Count has 1 means the encounter was edited and in Compliance1 Yes was changed to No, this is where the update happened, and third screenshot is how its suppose to  look like in the report once the variable is made.

Thanks
Ahmad
edit1.png (36.2 kB)
edit2.png (37.2 kB)
edit3.png (9.9 kB)
0

Hi Mahboob,

Let me know if you need more clarification.

Thanks

Ahmad

0
Mahboob Mohammed Nov 04, 2016 at 06:17 PM
0

Hi Ahmad,

Quick question: Are you getting the data for all those N Encounter IDs in the report?

Mahboob Mohammed

Share
10 |10000 characters needed characters left characters exceeded
Mahboob Mohammed Nov 04, 2016 at 06:39 PM
0

Hi Ahmad,

Why don't you create this Webi directly, instead of writing that Case statement in SQL? You'll have to bring in data for all the Scorecard IDs for an Encounter ID, in the report.

Try creating the below variables:

Final Scorecard ID = Last([Scorecard ID]) in ([Encounted ID])

Count of Scorecard Updates = If((Count([Scorecard ID]) in ([Encounter ID])) = 1) Then 0 Else (Count([Scorecard ID]) in ([Encounter ID]))

Finally, in the table, add as columns the below:

Final Scorecard ID, Count of Scorecard Updates, Encounter ID (right click and hide this Encounter ID column). Let us know if that helps.

Mahboob Mohammed

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thanks Mahboob, the formula is working which is great but its not showing the right updates because its suppose to be a count on specific score so not all the edits or changes will be counted. I've attached the screenshots to clarify it more. countnew2.pngeditnew3.pngeditnew1.png

Thanks

Ahmad

countnew2.png (37.2 kB)
editnew3.png (35.2 kB)
editnew1.png (63.8 kB)
0

Hi Ahmad,

What is that one specific Scorecard ID for which effects the Count? Please wrap the Count of Scorecard IDs with a Sum() function (as below) and see if that is what you want.

Count of Scorecard Updates = Sum(If((Count([Scorecard ID]) in ([Encounter ID])) = 1) Then 0 Else (Count([Scorecard ID]) in ([Encounter ID])))

Thanks,

Mahboob Mohammed

0
Ahmad Nazir Nov 08, 2016 at 04:59 PM
0

Thanks for helping Mahboob,

I was able to solve my problem using this formula =Count([Original Scorecard ID]Where (Previous([Compliance Bullet 1])<>[Compliance Bullet 1] And [Scorecard Active Indicator]=1)) and making all the variables and adding them all up at the end in a separate variable.


Thanks,

Ahmad

Share
10 |10000 characters needed characters left characters exceeded