6 Replies Latest reply: Feb 27, 2018 2:09 PM by Dragos Simionescu RSS

    Running Total (YTD) split by dimension

    Dragos Simionescu

      Hi, I'm pretty new to Qlik Sense but I have a pretty solid background in the Power BI/DAX way of doing things. I realise there are some different paradigms which I need to rethink in Qlik but so far I'm really enjoying it.

      As a learning project I'm trying to recreate an analysis I did in Power By which shows some headcount and attrition charts.

      My source data looks like this:

      NameEmailStarting DateLast Date
      Name1email1Date1Date2 (or null if still in place)
      Name2email2Date3Date4
      etc

      Charts I'm trying to produce:

      1. Monthly Adds vs Leavers - ok, I got this, I first unpivoted the Starting Date and Last Date so I can calculate one COUNT measure and then split the line chart by the Start/Last Dimension

      2. Headcount total accumulation - got this also, I used a simple Rangesum(above(count) for Adds - Leaves

      3. YTD (cumulative) Adds vs Leaves - so this one needs to reset every year start, not a total accumulation. Here's where it gets tricky for me:

       

      Formula I used: Rangesum(above(count(Email),0,[Roles_Res.Data field.autoCalendar.Month]))

      Everything works perfectly until I try to split it by the Start/Left dimension. I simply don't understand the results which this produces so I can try to track the root cause of the problem.

      If I use the exact same dimensions and measure and do a Pivot Chart the data calculates correctly, as soon as I convert this to a Line chart the numbers are wrong.

      If I calculate two measures, one for Adds, one for Leavers, it also works correctly, and I suppose it could be a workaround, but I would really like to understand what's the problem with the initial approach.

       

      Some screenshots to exemplify the issue. Any ideas what I'm doing wrong?

      Pivot correct.JPG

      Unsplit correct.JPG

      One measure dim split incorrect.JPG

      Two measures correct.JPG