topic Re: Total row for counting number of active customers by month? in QlikView

Total row for counting number of active customers by month?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Total row for counting number of active customers by month?

asgardd2 — Fri, 08 Dec 2017 12:51:30 GMT

Hello!

I think, the simple solution is calculate "Active" flag in the script. (last three months)

Sample app. int the attachment.

Re: Total row for counting number of active customers by month?

sunny_talwar — Fri, 08 Dec 2017 12:52:23 GMT

Before is a function which is specifically use for Pivot table.... when you use Aggr(), it doesn't work anymore, because Pivot behaves more like a straight table and you have to fall back to above function....

Secondly, you have use Month before Year in your Aggr(), I think you might need to switch them because in your chart it seems that you have Year before Month as your dimension order. This matters, because the order of the fields determine the sorting.

Finally, I would suggest using a field which is a combination of Month and Year instead of Month & Year separately because if you have three fields and you use TOTAL... it will not stop looking above once you reach the end of a customerID... better to not use TOTAL... but then to crossover year, you will have to make sure you use a single MonthYear field....

So, try something like this

Sum(Aggr(If(RangeSum(Above(Active, 0, 3)) = 3, 1, 0), CustomerID, MonthYear))

Re: Total row for counting number of active customers by month?

Anonymous — Mon, 11 Dec 2017 06:56:19 GMT

Hi,

Thanks for your replies. I changed the script to load a "MonthYear" field as mentioned (as it's apparently not possible to use a calculated dimension in aggr() function - Using a calculated dimension in Aggr() | Qlik Community), switched the pivot table's layout to make it like a straight table and tried your formula but it did not seem to work. My formula is:

"Sum(Aggr(If(RangeSum(Above(Active, 0, 3)) = 3, 1, 0), CustomerID, MonthYear))"

The formula still works for each AccountID & MonthYear if I remove the "Sum(aggr())" part.

Thanks,

Henry

Re: Total row for counting number of active customers by month?

sunny_talwar — Mon, 11 Dec 2017 09:39:32 GMT

Do you have QV 12 or above or if you are working with Qlik Sense, you might be able to give this a try

Sum(Aggr(If(RangeSum(Above(Active, 0, 3)) = 3, 1, 0), CustomerID, (MonthYear, (NUMERIC))))

Re: Total row for counting number of active customers by month?

Anonymous — Tue, 12 Dec 2017 01:30:55 GMT

Hi,

I am using Qlikview 12. I tried your formula but couldn't succeed. I guess I am left with using script?

Regards,

Henry

Re: Total row for counting number of active customers by month?

sunny_talwar — Tue, 12 Dec 2017 10:03:35 GMT

Would you be able to share a sample to check it out?

Re: Total row for counting number of active customers by month?

Anonymous — Tue, 12 Dec 2017 11:10:06 GMT

Hi Sunny,

Please find attached the file. Thanks!

P.S. Just wonder if I can group by "CustomerID" first and then load the data into a straight table, the problem would be solved?

Regards,

Henry

Re: Total row for counting number of active customers by month?

sunny_talwar — Tue, 12 Dec 2017 13:49:34 GMT

Is this what you are after?

Re: Total row for counting number of active customers by month?

Anonymous — Wed, 13 Dec 2017 01:28:06 GMT

Hi Sunny,

Thanks for this. I would prefer to show total number of active users every month. Can you explain to me how you arrived with this?

Regards,

Henry

Re: Total row for counting number of active customers by month?

sunny_talwar — Wed, 13 Dec 2017 09:20:30 GMT

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

Re: Total row for counting number of active customers by month?

Anonymous — Wed, 13 Dec 2017 09:41:57 GMT

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

Re: Total row for counting number of active customers by month?

sunny_talwar — Wed, 13 Dec 2017 09:45:15 GMT

How about this

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

Re: Total row for counting number of active customers by month?

Anonymous — Wed, 13 Dec 2017 11:03:17 GMT

Hi Sunny,

The formula works. Thanks a lot!

Regards,
Henry

Re: Total row for counting number of active customers by month?

Anonymous — Thu, 14 Dec 2017 03:59:56 GMT

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

Re: Total row for counting number of active customers by month?

sunny_talwar — Thu, 14 Dec 2017 12:27:14 GMT

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))