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

# Comparison to last year by calculated dimension

Hi,

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

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!