Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Total row for counting number of active customers by month?

Hi guys,

I created a pivot table (screenshot below):

PivotTable.PNG

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

1 Solution

Accepted Solutions
sunny_talwar

How about this

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

Capture.PNG

View solution in original post

15 Replies
asgardd2
Creator III
Creator III

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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?

Capture.PNG

Anonymous
Not applicable
Author

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