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

[asgardd2]

Hello!

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

Sample app. int the attachment.

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

Hi,

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

Regards,

Henry

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

Is this what you are after?

[Anonymous]

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