topic Re: Combining Set Analysis with AGGR - Assistance Needed in QlikView

Combining Set Analysis with AGGR - Assistance Needed

Mon, 22 Sep 2014 18:01:54 GMT

I need to show total number of orders placed within 30 days prior to the date each person first used selected coupon code.

So i need to see the first date each person used the selected coupon code, then how many orders (with any coupon code) they placed in the 30 days prior to that date, then sum that count at a global level. Everything i have tried so far just keeps applying the date range at a global level instead of per person (i.e. if the coupon was first used by anyone on 3/1/14, then it just counts all orders placed 30 days prior to 3/1)

I'm thinking this requires something like the below, but this returns no results:

sum(

AGGR(

count(DISTINCT {<Date = {"$(= '<' & min({<Coupon = $(vCoupon)>} Date) &

'>=' & min({<Coupon = $(vCoupon)>} Date)-10)"} >} Orderid),Person))

I've attached a test doc that has some sample data, and i'm using the text box to test out expressions. Any help or questions to clarify are welcomed. Thanks!

Re: Combining Set Analysis with AGGR - Assistance Needed

jerem1234 — Mon, 22 Sep 2014 18:54:05 GMT

You won't be able to use set analysis here, since each date every person used the coupon will be different (set analysis is evaluated on the whole set, not individually). You'll have to use an if statement like:

=count(distinct aggr(if(Date<min(total <Person> {<Coupon = {'$(vCoupon)'}>} Date) and Date>=min(total <Person> {<Coupon = {'$(vCoupon)'}>} Date)-10, Orderid), Person,Date))

Hope this helps!

Re: Combining Set Analysis with AGGR - Assistance Needed

Mon, 22 Sep 2014 19:07:53 GMT

Thanks Jeremiah.

I did have a variation of this almost working with an IF statement originally, and this one does appear to get what i need, but due to the amount of records i'm dealing with (>375MM) it takes an excessively long time (i.e. 30 minutes!) to render the chart displaying the data. I read somewhere on the Community that it was a best practice to use Set Analysis in place of IF statements to optimize processing.

Is there no way to concatenate the statement to use the set analysis? Or other approach?

Re: Combining Set Analysis with AGGR - Assistance Needed

jerem1234 — Mon, 22 Sep 2014 20:19:18 GMT

Well since the min date changes for each person, set analysis can't be done (set analysis evaluates on the whole set and won't change the min(Date) for each person). So I think the best option is putting as much as you can in the loadscript. This will increase reload time a bit, but will improve the time the chart renders on front-end. For this, we can create flags. So I added this script in:

//Added code here

Left Join(temp)

Table1:

Load Person, min(Date) as XYZMinDate

Resident temp

where Coupon='XYZ'

Group by Person;

//End Added Code

Data:

load Person,Orderid,Date,Coupon,

num(Date) as numDate,

//Added code here

if(Date < XYZMinDate and Date >= XYZMinDate -10, 1, 0) as XYZMinFlag

//End Added Code

resident temp;

Unfortunately, you'll have to add a flag for each coupon (so if you have many coupons, might have to change this up by maybe making a loop or entirely different approach).

Then used the expression:

count({<$(vCoupon)MinFlag = {1}>} distinct Orderid)

Please find attached.

Hope this helps!

Re: Combining Set Analysis with AGGR - Assistance Needed

Tue, 23 Sep 2014 00:37:47 GMT

OK, I see what you're doing here, and this was one approach that crossed my mind, but unfortunately i'm dealing with hundreds of coupon codes and the list grows all the time, so hard-coding a field for each coupon isn't really going to work. Additionally i have several time buckets in which to count orders (30, 60, 90 days, etc).

I guess i'm back to the drawing board unless anyone has other ideas.

Re: Re: Combining Set Analysis with AGGR - Assistance Needed

jerem1234 — Tue, 23 Sep 2014 17:57:07 GMT

What you could try is doing a loop. It will make your loadscript complex and may take a bit of time to reload, but the dashboard experience should improve. I have attached an example real quick that works with your example. You can use some of it or try to improve but you'll have to test out and see if it improves the time the chart renders. Check the expression in the red textbox as well the script. For the buckets, you can add a new field for each bucket to my script.

Also like you said, maybe there is a different approach you can do.

Hope it helps!

Re: Combining Set Analysis with AGGR - Assistance Needed

Mon, 06 Oct 2014 14:53:20 GMT

Thanks so much, after i finally took the time to understand what you were doing here, it works like a charm! The reload time is huge, but i'd rather have that than slow rendering.