Total row for counting number of active customers by month?

[Anonymous]

Hi guys,

I created a pivot table (screenshot below):

Basically "1" & "0" here are for determining the status of the customer: If customer always bought something in every month over the last 3 months (this month, previous month and the month before the previous month), I marked them as Active ("1"). For this purpose, I used the function

if(rangesum( before( TOTAL Active,0,3) ) = 3, 1,0)

For the "Total" row, I wanted to show number of "Active" customer for the month (I supposed it would be just the sum of all rows). I tried using Sum(aggr()) mentioned in this link Sum of rows in pivot tables ‒ QlikView but couldn't succeed. My function is:

sum(aggr(if(rangesum( before( TOTAL Active,0,3) ) = 3, 1,0),CustomerID,Month,Year))

Any help/insight will be really appreciated! Thank you!

Henry

[sunny_talwar]

Just so I understand, what is that you don't understand? Have you looked at the qvw and don't understand the expression or do you just want to see the expression?

Expression is this

Sum(Aggr(If(RangeSum(Above(If(Sum(Purchases) >= 5, 1, 0), 0, 3)) = 3, 1, 0), Date, CustomerId))

Also, you mentioned seeing all months, do you mean something like this?

Just uncheck 'Suppress zero values' on the presentation tab.

Best,

Sunny

[Anonymous]

Hi Sunny,

Thanks for your reply. Sorry I don't know why but I couldn't open any .qvw file (the same for Anton's file) . As far as I know your latest formula will be based on condition: sum of purchases over the last 3 months >= 5 or not. My condition is actually: sum of purchases in each month over the last 3 months  >= 5 (Sorry again for not being clear). From this condition, you can see for the first 2 months (Jan-15 & Feb-15), as we don't have 3 months data there should not be any "Active" customers.

Regards,
Henry

[sunny_talwar · Accepted solution]

How about this

Sum(Aggr(If(RangeSum(Above(If(Sum(Purchases) >= 5, 1, 0), 0, 3)) = 3, 1, 0), CustomerId, Date))

[Anonymous]

Hi Sunny,

The formula works. Thanks a lot!

Regards,
Henry

[Anonymous]

Hi Sunny,

I just checked with my source data again and apparently there was a problem: Using the formula mentioned above

“Sum(Aggr(If(RangeSum(Above(If(Sum(Purchases) >= 5, 1, 0), 0, 3)) = 3, 1, 0), CustomerId, Date))”

will ignore the month if the customer did not have any data for e.g. we are looking at Jun-16 and if customer A did not have any data in May, the formula will look at data in Jun, Apr & Mar. If these 3 months satisfy the condition, customer A will still be marked as “Active”.

Regards,

Henry

[sunny_talwar]

Try the The As-Of Table approach

Script

Table:

LOAD Date,

    CustomerId,

    Purchases

FROM

[Sample1.xlsx]

(ooxml, embedded labels, table is Sheet1);

AsOfTable:

Left Keep (Table)

LOAD DISTINCT Date as AsOfDate,

AddMonths(Date, -IterNo() + 1) as Date,

-IterNo() + 1 as Flag

Resident Table

While IterNo() <= 3;

Dimension

AsOfDate

CustomerId

Expression

Sum(Aggr(If(Sum(Aggr(If(Sum(Purchases) > 5, 1, 0), AsOfDate, Date, CustomerId)) = 3, 1, 0), AsOfDate, CustomerId))