Skip to Content
avatar image
Former Member

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

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'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Nov 04, 2016 at 04:12 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 04, 2016 at 06:17 PM

    Hi Ahmad,

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

    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 04, 2016 at 06:39 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Nov 08, 2016 at 04:59 PM

    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

    Add comment
    10|10000 characters needed characters exceeded