Hello,
I have a particular expression that I need to use but it is proving to be quite complicated (see below current expression). Basically what I want to do is sum the amount of packs based on a number of criteria using a two date variables. In orange I only want want to sum the data which is between the vMaxDate and 21 days before it of the ReportDate, then in green I only want to sum the data which is between 161 and 449 days based on the vMaxDate and vMinDate of the Report Date, and once these two conditions have been met I want to sum the packs between the vMinDate and vMaxDate of the report.
The below expression works fine when I have the Customer, Product and ReportDate as dimensions but essentially I want a summary table to only have Product as the dimension (I know that aggr needs to be used however I'm having no luck filtering out based on the previous if statements).
if(max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}ReportDate >max(date(vMaxDate)-21),
if(max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}Days) >161 and max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}Days) <449),
sum({<ReportDate={">$(=date(vMinDate)) <=$(=date(vMaxDate))"}>}Packs)))
RawData:
| Customer | Product | ReportDate | Days | Packs |
|---|
| CustomerA | A | 20/07/2013 | 235 | 4000 |
| CustomerA | A | 18/07/2013 | 500 | 5000 |
| CustomerA | A | 06/06/2013 | 200 | 6000 |
| CustomerB | A | 16/07/2013 | 350 | 2000 |
| CustomerB | A | 13/05/2013 | 500 | 7000 |
| CustomerC | B | 21/07/2013 | 600 | 400 |
| CustomerC | B | 13/07/2013 | 300 | 5000 |
OutputTable: (assuming vMaxDate is 26/07/2013 and vMinDate is 01/05/2013)
| Product | Packs | ReportDate |
|---|
| A | 6000 | 20/07/2013 |
| B | 5000 | 21/07/2013 |
From the RawData table rows 3 and 5 do not meet the criteria of being inside 21 days of the vMaxDate so should not be considered, whilst rows 2 and 6 do not meet the criteria of being between 161 and 449 days, which leaves row 1, row 4 and row 7.
Thanks,
Ralph
