topic How to Aggr(count(CustomerNr),(CustomerNr) ) in script in QlikView

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

Mon, 26 Mar 2012 12:31:14 GMT

Hi all you great people

I hope someone can help me.

I have this calculated dimension =Aggr(count(CustomerNr),(CustomerNr) ), to show how many times customers are shopping over the period I those.

Every time the customer shops, the use the same CustomerNr.

My problem is that there are around 1 mill. Rows with CustomerNr and my PC is almost crashing every time I those a new period.

Therefore I thought that I have to have that calculation in the script, but how do I do that ?

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

Clever_Anjos — Mon, 26 Mar 2012 17:13:28 GMT

Well, AGGR is not present at script time.

You should calculate with a separated LOAD ... RESIDENT ... GROUP BY CustomerNr

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

Mon, 02 Apr 2012 09:54:24 GMT

Hi Clever

Thanks for the answer. Unfortunately it don’t give me the correct result.

When I use this, and the customer has purchased 2 times, it will always count as 2, regardless that I don’t choose the month 1 of the purchase are from.

So it should in that example only be 1.

This is what I want:

CustomerNr Month

12 January

12 March

I would like the result to be:

If I chose January the customers has 1 purchase
If I choose Jan, Feb and Mar the customer has 2 purchase
If I choose March the customer has 1 purchase
If I choose February the customer has 0 purchase

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

sivarajs — Mon, 02 Apr 2012 10:13:42 GMT

You are counting and grouping by the same field obviously it will give 1

Try to group by some other field

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

Mon, 02 Apr 2012 10:58:15 GMT

I think using group by in script will not solve your purpose it will generate data for whole table and when you apply filters , it will generate wrong data (i.e total count after applying group by).

To calculate group by for each selection, you have to use it in chart.

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

jagan — Mon, 02 Apr 2012 10:59:59 GMT

Hi,

Add an additional column as shown below

LOAD

1 AS CustomerCount

RESIDENT DataSource;

Now in your expression use Sum(CustomerCount) and use CustomerNr as Dimension. Count uses more memory and it degrades the performance. Try using the above logic, how this fine tunes the performance.

Regards,

Jagan.

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

Mon, 02 Apr 2012 11:13:16 GMT

Hi thanks

I don't think this will give me what I want.

I need to use the calculated Number of purchase as a Dimension and I want to be able to choose number of month to see over as I choose and the data has to follow.

The expressions will be Count(distinct(CustomerNr))

It works with the Aggr(count(customerNr),(CustomerNr)) as a calculated dimension

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

Clever_Anjos — Mon, 02 Apr 2012 11:32:53 GMT

Count(DISTINCT) instead of
Best regards

How to Aggr(count(CustomerNr),(CustomerNr) ) in script

jagan — Mon, 02 Apr 2012 11:38:03 GMT

Hi,

Try this, it would be faster than your expression

LOAD

1 AS CustomerCount

RESIDENT DataSource;

Aggr(Sum(CustomerCount),(CustomerNr))

Regards,

Jagan.