topic Aggr function not working in QlikView

Aggr function not working

Thu, 30 Oct 2014 07:46:11 GMT

Hi,

I am trying to get the number of customer that didn't make purchases in the current selected month but did buy the previous month, I am using set analysis to get the previous month as the user might not select it. See my expression below:

sum(aggr (if(sum({<YYYYMM={$(vPrevMonthMaxYM)},Year=,Month=>} Sales)<>0 and sum(if(YYYYMM=vMaxYM, Sales,0)) = 0,1,0), [Store Number], [Account Code]))

I have the following dimensions in my chart: region and store.

It is not working, it is returning less count than I expect.

I checked my logic by looking at [Store Number] and [Account Code] as dimensions and if(sum({<YYYYMM={$(vPrevMonthMaxYM)},Year=,Month=>} Sales)<>0 and sum(if(YYYYMM=vMaxYM, Sales,0)) = 0,1,0) as an expression in a table chart and it is giving me what I want.

What am I doing wrong?

I am using the same aggr function to count the number of customers who did not make purchases in the 6 months prior to the selected (max) month but have started buying in the select (max) month, and that is working 100s. And I use the same ... somewhere else and is working.

Re: Aggr function not working

diego_alegre_nu — Thu, 30 Oct 2014 07:54:37 GMT

Could it be possible of a customer buying in two stores 6 months ago, and just in one of them during the last month?

then you could get different results in both expressions explained.

Re: Aggr function not working

Thu, 30 Oct 2014 08:29:02 GMT

A customer has an account per store, if he/she has two accounts in different stores we count him/her as two customers, different accounts. This is a chain liquor business on the redistribution/delivery side - selling and delivering to business i.e. hotels, restaurants, etc.

The one that is not working is where I am looking at those customers/accounts who bought last month but not this month.

The other ones are working fine and are not related.

Re: Aggr function not working

Kushal_Chawda — Thu, 30 Oct 2014 08:43:55 GMT

Can you post the sample application with output needed so that t will be more clearer?

Re: Aggr function not working

jonathandienst — Thu, 30 Oct 2014 08:45:48 GMT

If the customer did not buy in the current month, then I assume there are no records in the data for that store / account / month, so in

sum(if(YYYYMM=vMaxYM, Sales,0)) = 0

the sum evaluates to Missing and your condition will always return false. You could try:

Alt(sum(if(YYYYMM=vMaxYM, Sales,0)), 0) = 0

Alt(sum({<YYYYMM={$(vMaxYM)}>} Sales), 0) = 0

If that does not work, try switching the condition around:

.... Alt(sum({<YYYYMM={$(vMaxYM)}>} Sales), 0) > 0, 0, 1)

HTH

Jonathan

Re: Aggr function not working

Thu, 30 Oct 2014 12:30:59 GMT

Yes you are correct Jonathan, there are no records in the data for the month selected except for zero sales for commission accounts that are hard-coded in the load script (don't ask - long story - previous developer), these are the only accounts that I am getting in my results, I am getting the same results with all your suggestions.

I must note though if I include the previous month in my selection I do get the correct results but this is not ideal - I need a fix for one month selection.

Re: Aggr function not working

jonathandienst — Thu, 30 Oct 2014 13:06:23 GMT

The ideal solution for this is in the load script. You could create dummy records, although these can mess with counts. Another approach is for the load script to create a flag table, with period, account, store and a flag to indicate whether there are records or not. This should be populated for all possible combinations, and the flag will be set if there are any real transactions against those dimensions and the other combinations get a zero to indicate no transactions.

Then instead of summing sales in the front end, you can get the flag value, which will always exist, if done correctly.

HTH

Jonathan