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

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

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?

Capture.PNG

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

Best,

Sunny

Highlighted
Contributor II
Contributor II

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

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?

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

Highlighted
Contributor II
Contributor II

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

Hi Sunny,

The formula works. Thanks a lot!

Regards,
Henry

Highlighted
Contributor II
Contributor II

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

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

Highlighted

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

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