1 Reply Latest reply: Apr 5, 2017 6:59 AM by AC BC RSS

    Alternate states in a calculated dimention

    Heather Montgomery

      Hi

       

      I have used alternate states in two charts one set to a start field the other to and end field.(the field they use is from the my calendar YEARWEEK i.e 201701)

       

      The charts both show the count of customers that fit a combination of two status against the dates chosen by alternate state start and alternate state end.

       

      My data contains fields StartStatus, and EndStatus, these are linked to the field YEARWEEK in the calendar through an interval match in the load script.

       

      This is the part I need a little help with please: I want to show in the 3rd chart, the changes in status from start (Alternate State Start) and end (Alternate State End) points

       

      I have an IF statement for each status i.e:

       

       

      IF(StartStatus=EndStatus,'1.SameStatus',

      IF(EndStatus='status1A',

      IF(StartStatus = 'status1B','2.StatusUp',

      IF(StartStatus = 'status1C','2.StatusUp',

      IF(StartStatus='status1D,'2.StatusUp',

      IF(StartStatus='status1E','2.StatusUp','3.StatusDown')))),'3.StatusDown'))

       

      The expression =Count(distinct ID)

       

      How do I amend the calculated dimention to select the start status to pick up the relationship to Alternative State Start, and the EndStatus to Alternative State End?

       

      I've not used Alternate States before and athough i've read through the manual, i've not seen an example where it's used in a calculated dimention, so currently a little stuck.

       

      Any pointers would be most appreciated - thanks in advance