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

[#1]

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 ?

[Clever_Anjos]

Well,  AGGR is not present at script time.

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

[#3]

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

[sivarajs]

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

Try to group by some other field

[#5]

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.

[jagan]

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.

[#7]

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

[Clever_Anjos]

Count(DISTINCT) instead of
Best regards

[jagan]

Hi,

Try this, it would be faster than your expression

LOAD

'

'

'

'

1 AS CustomerCount

RESIDENT DataSource;

Aggr(Sum(CustomerCount),(CustomerNr))

Regards,

Jagan.