Complicated Set analysis

Fri, 26 Jul 2013 09:01:49 GMT

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

Re: Complicated Set analysis

Tammy_Milsom — Fri, 26 Jul 2013 14:34:52 GMT

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))

Re: Complicated Set analysis

Mon, 29 Jul 2013 09:05:58 GMT

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

topic Re: Complicated Set analysis in QlikView

Complicated Set analysis

Re: Complicated Set analysis

Re: Complicated Set analysis