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.