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.
