Forum Discussion

MikeG's avatar
MikeG
Cloud Apps
10-04-2024
Solved

Running Rate

Hi folks,

hoping someone can help me with a Running Rate.

I have pasted an example of how I have accomplished this in Looker, but I cant seem to figure it out in Sisense.

To Level set, in my Dashboard I have a Date Filter called Placement Dates.

I then want to calculate the Running Cure Rate by DE Length

to calculate the running cure, in Looker I would do a running sum of accounts that have cured/total placements(for the time period selected in the filters)

this is what it looks like.

DE_LengthChampion Running Cure PercentageChallenger Running Cure Percentage
00.12%0.12%
117.97%17.58%
223.96%24.80%
330.42%30.85%
436.05%36.47%
541.33%40.71%
644.51%43.84%
749.15%47.80%

I cant seem to do the same in Sisense.

Dashboards & Reporting
  • david-h's avatar
    david-h
    10-09-2024

    Thanks DRay to tag me !

    MikeG , you can see each row as an individual calculation. For each of them, the engine applies a filter on DE_LENGTH. So the cells on first row are calculated with DE_LENGTH=0, second row DE_LENGTH=1, and so on.

    In order to have a calculation not being influenced by this filter (Power BI would call it context) you need to add a filter to your measure : , all([DE_Length]) )

    So in your pivot : [Running Cures] / ([Total Placements] , all ([DE_Length]) )

    Be careful, if you use explicitly SUM, it would be :

    [Running Cures] / (SUM ([Placements]) , all ([DE_Length]) )

    Hope this helps.

    Best,David.

6 Replies

  • david-h's avatar
    david-h
    Cloud Apps
    10-06-2024

    Hi MikeG,

     

    Not sure how you do it in Looker, or what your tries look like in Sisense. But it looks like you have the right method.

    The complete formula should looks like :

    RSUM([Total of Number of accounts cured]) / ( [Total of Number of accounts cured] , all([DE_Length]) )

    Best, David.

  • david-h's avatar
    david-h
    Cloud Apps
    10-09-2024

    Thanks DRay to tag me !

    MikeG , you can see each row as an individual calculation. For each of them, the engine applies a filter on DE_LENGTH. So the cells on first row are calculated with DE_LENGTH=0, second row DE_LENGTH=1, and so on.

    In order to have a calculation not being influenced by this filter (Power BI would call it context) you need to add a filter to your measure : , all([DE_Length]) )

    So in your pivot : [Running Cures] / ([Total Placements] , all ([DE_Length]) )

    Be careful, if you use explicitly SUM, it would be :

    [Running Cures] / (SUM ([Placements]) , all ([DE_Length]) )

    Hope this helps.

    Best,David.

  • DRay's avatar
    DRay
    Admin
    10-07-2024

    Hello MikeG,

    I’m following up to see if the solution offered by david-h worked for you.

    If so, please click the 'Accept as Solution' button on the appropriate post, that way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

    Thank you.

    • MikeG's avatar
      MikeG
      Cloud Apps
      10-08-2024

      so it didnt work.
      here is what it looks like when I do this in Looker. Notice the total placements is static, and only changes when I apply a filter on a date range.

      DE_LENGTH

      		Running CuresTotal PlacementsRunning Cure Rate
      010100000.10%
      125100000.25%
      245100000.45%
      3100100001.00%
      4300100003.00%
      5450100004.50%
      6556100005.56%
      7685100006.85%
      8978100009.78%
      910561000010.56%
      1025001000025.00%

      when I try to replicate, Total Placements, is being influenced by DE_Length and it looks like this
      Need to figure out how to get the Total Placements to be the actual Total of the date range Im looking at and not be based on the DE_Length, Hope this makes sense