0 Replies Latest reply: Jul 8, 2017 6:50 AM by Max Shevchenko RSS

    Comparison to last year by calculated dimension

    Max Shevchenko

      Hi,

       

      LOAD * Inline [

      Date, Customer, Quantity

      01/07/17, Customer1, 1

      02/07/17, Customer1,  2

      03/07/17, Customer1,  3

      01/07/16, Customer2,  1

      02/07/16, Customer1,  3

      02/07/16, Customer2,  4

      03/07/16, Customer1,  3

      ];

       

      From the data above I have created table with one dimension and two expressions.

      Dimension is calculated

      =if(Aggr(sum({<Date = {'>=(=Min(Date))<=$(=Max(Date))'}>} Quantity),Customer)<=4,'Group1',

      if(Aggr(sum({<Date = {'>=(=Min(Date))<=$(=Max(Date))'}>} Quantity),Customer)<=10,'Group2',

      if(Aggr(sum({<Date = {'>=(=Min(Date))<=$(=Max(Date))'}>} Quantity),Customer)<=12,'Group3'

      )))

       

      Expression1

      sum(Quantity)

       

      Expression2

      sum({$<Date = {'>=$(=AddYears(Date,-1))<=$(=MonthEnd(AddYears(Date,-1)))'}>} Quantity)


      As result I'm trying to get a compared column('Period2') for the same date of a last year by GroupName.


      Now it makes comparison to a customer. For example if you select date '02/07/2017' you will see

      GroupName | Period1 | Period2 |

      Group2          |             2 |             7 |

       

      If we check date '02/07/2016' will find

      GroupName | Period1 | Period2 |

      Group1          |             3 |             0 |

      Group2          |             4 |             0 |


      In this table we see that Group2 has quantity '4' .


      So when I select the date '02/07/2017' I am looking for the result as below

      GroupName | Period1 | Period2 |

      Group2          |             2 |             4 |


      It should be compared by group but with the same conditions as in calculated dimension.


      If you still on this point of reading and know how to help me please write your solution!

      Thanks in advance.