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

[Former Member]

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'

[Former Member]

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

[mhmohammed]

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

[Former Member]

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

[mhmohammed]

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

[mhmohammed]

Hi Ahmad,

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

Mahboob Mohammed

[mhmohammed]

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

[Former Member]

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

[Former Member]

Hi Mahboob,

Let me know if you need more clarification.

Thanks

Ahmad