Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
i have one big data table containing:
Date | Product | Price |
23-Apr | 1 | 100 |
24-Apr | 1 | 100 |
25-Apr | 1 | 120 |
26-Apr | 1 | 120 |
27-Apr | 1 | 120 |
28-Apr | 1 | 120 |
23-Apr | 2 | 430 |
24-Apr | 2 | 430 |
25-Apr | 2 | 430 |
26-Apr | 2 | 430 |
27-Apr | 2 | 430 |
28-Apr | 2 | 430 |
23-Apr | 3 | 50 |
24-Apr | 3 | 40 |
25-Apr | 3 | 40 |
26-Apr | 3 | 50 |
27-Apr | 3 | 50 |
28-Apr | 3 | 40 |
23-Apr | 4 | 250 |
24-Apr | 4 | 250 |
25-Apr | 4 | 250 |
26-Apr | 4 | 250 |
27-Apr | 4 | 250 |
28-Apr | 4 | 280 |
when selecting 2 dates (28 apr & 23 apr)
i want to obtain the following chart table, comparing the evolution of price from min(date) = 23 apr until max(date) = 28 apr:
Price Increase | Price constant | Price decrease | |
No Products | 2 | 1 | 1 |
how can i do this?
Thx,
Andrei
Create a Straight Table
Use Calculated Dimension
='Number Of Products'
Expressions
Price Increase
SUM(Aggr(IF(Only({<Date = {"$(=Date(Min(Date)))"}>}Price) - Only({<Date = {"$(=Date(Max(Date)))"}>}Price) > 0, 1),Product))
Price Constant
SUM(Aggr(IF(Only({<Date = {"$(=Date(Min(Date)))"}>}Price) - Only({<Date = {"$(=Date(Max(Date)))"}>}Price) = 0, 1),Product))
Price Decrease
SUM(Aggr(IF(Only({<Date = {"$(=Date(Min(Date)))"}>}Price) - Only({<Date = {"$(=Date(Max(Date)))"}>}Price) < 0, 1),Product))
HTH
Regards,
MK
Try this.
or may be this
=sum(aggr(if (sum({<Date= {"$(=date(max(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price)> sum({<Date= {"$(=date(min(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price),1,0),Product))
=sum(aggr(if (sum({<Date= {"$(=date(max(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price)< sum({<Date= {"$(=date(min(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price),1,0),Product))
=sum(aggr(if (sum({<Date= {"$(=date(max(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price)= sum({<Date= {"$(=date(min(date#(Date,'DD-MMM')),'DD-MMM'))"}>}Price),1,0),Product))
Hi,
another solution might be:
=Aggr('Price '&Pick(Sign(FirstSortedValue(Price,-Date)-FirstSortedValue(Price,Date))+2,'decrease','constant','increase'),Product)
hope this helps
regards
Marco