4 Replies Latest reply: Aug 19, 2015 7:21 AM by Daniel Balchasan RSS

    Slowly changing dimension Day to Day comparison

    Daniel Balchasan

      Hey everyone.

       

      I've been trying to work out how to make a line chart for a few days now and I'm really stuck, I hope someone can help me.

       

      I'm trying to show the 'Net gain' change over time.

       

      I have a data model that includes a slowly changing dimension  (Member State) and a master calendar. I define my 'Net gain' as the difference in 'Number of active members Today' VS the 'Number of active members Yesterday'.

       

      I am also able to calculate the 'Net gain' fairly easily and place it in KPI's or pie charts. I simply use :

       

      Count({$<Data_DATE = {'$(vMaxDate)'},STATEID ={'MEMBER'}>}EXTERNAL_ID)

      -

      Count({$<Data_DATE ={'$(vMaxDayBefore)'},STATEID ={'MEMBER'}>} EXTERNAL_ID)

       

      This means I count the number of 'EXTERNAL_ID's (Unique person ID) which have a state of 'MEMBER' today (vMaxDate), and subtract the same calculation for yesterday (vMaxDayBefore).

       

      However, this doesn't seem to work when I try to use this measure in a line chart, where the dimension is Data_DATE.

       

      Am I missing something?

      Shouldn't it use the results of the calculation in the measure for each Data_DATE as the value the the Y Axis?

       

      I would appreciate any input, especially from someone who's done something similar before.

       

      Thanks