Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted

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

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
Highlighted
Creator III
Creator III

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

Hello!

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

Sample app. int the attachment.

Highlighted

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

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

Contributor II
Contributor II

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

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

Highlighted

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

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

Highlighted
Contributor II
Contributor II

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

Hi,

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

Regards,

Henry

Highlighted

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

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

Highlighted
Contributor II
Contributor II

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

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

Highlighted

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

Is this what you are after?

Capture.PNG

Highlighted
Contributor II
Contributor II

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

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