Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Complicated Set analysis

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:

CustomerProductReportDateDaysPacks
CustomerAA20/07/20132354000
CustomerAA18/07/20135005000
CustomerAA06/06/20132006000
CustomerBA16/07/20133502000
CustomerBA13/05/20135007000
CustomerCB21/07/2013600400
CustomerCB13/07/20133005000

OutputTable: (assuming vMaxDate is 26/07/2013 and vMinDate is 01/05/2013)

ProductPacksReportDate
A600020/07/2013
B500021/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

1 Solution

Accepted Solutions
tmm
New Contributor III

Re: Complicated Set analysis

Try, not the most efficient with the constant use of if statements.

sum

(AGGR(

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))"}>}Days))),ReportDate))

2 Replies
tmm
New Contributor III

Re: Complicated Set analysis

Try, not the most efficient with the constant use of if statements.

sum

(AGGR(

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))"}>}Days))),ReportDate))

Not applicable

Re: Complicated Set analysis

Many thanks for that. As you said it's not the most efficient but I'm hopefully not going to have to do something like that again!

Thanks,

Ralph

Community Browser