22 Replies Latest reply: Nov 29, 2017 9:17 PM by Hung dep

# How to distinct count cumulative with date ranges

Hi experts,

I got data like this table:

 Outlet ID Date Sales Order status 111 11/1/2017 10 Approved 112 11/1/2017 20 Rejected 113 11/1/2017 30 Rejected 111 11/1/2017 90 Rejected 118 11/1/2017 120 Approved 111 11/4/2017 40 Approved 113 11/4/2017 60 Approved 115 11/4/2017 100 Approved 117 11/4/2017 110 Rejected 119 11/4/2017 130 Rejected 112 11/5/2017 50 Rejected 114 11/5/2017 70 Approved 115 11/6/2017 80 Approved 112 11/7/2017 50 Approved 114 11/7/2017 70 Rejected

I want to count cumulative outlets by date like this:

 Date Count distinct Outlet ID with total sales >=45, order status ="approved" Count cumulative Outlet with sales >=45 and order status ="approved" 11/1/2017 1 1 11/4/2017 2 4 11/5/2017 1 5 11/6/2017 1 5 11/7/2017 1 6 Grand Total 6 6

Can anyone help me the expression for the two ways of counting outlet ID, especially the second count which means Total cumulative outlets with sales >=45 and sales order status must be "approved" only.

Thank you a lot from Vietnam.

• ###### Re: How to count cumulative with date ranges

Hi,

Can you try :

=rangesum(Above(Count({<[Order status]={'Approved'},Sales={">45"}>}[Outlet ID]),0, RowNo()))

PFA.

• ###### Re: How to count cumulative with date ranges

Thanks Mohammed but it's not really correct.

Your solution does not Sum(Sales) by outlet ID. Eg: outlet ID 111, day 11/1: sales = 10 and day 11/4: sales = 40 --> Total sales for outlet 111 should be 50 (status approved). Then by date 11/4 we should have 4 outlets with sales >= 45, your expression only count 3.

One more thing with Rangesum is that it will sum all value by dimension:

Eg:

Day, outlet, sales

day1, A, 100

day1, B, 200

day 2, A, 50

-> result (Cumulative count):

day 1: 2 outlets

day 2: 1 outlet + 2 of the previous day = 3 outlets. However, this should be 2 outlets only as outlet A is repeated, not new one.

• ###### Re: How to count cumulative with date ranges

Can anyone help me...

• ###### Re: How to distinct count cumulative with date ranges

NoConcatenate
test2:
Date,
ID,
Status,
only(Sales) as Sales,
if(ID=peek('ID'),if(peek('CumSales')<45,peek('CumSales')+only(Sales),0),only(Sales)) as CumSales
RESIDENT tesT
where Status='Approved'
group by Date,ID,Status
order by ID, Date;

Concatenate(test2)
Date,
ID,
Status,
only(Sales) as Sales,
only(Sales) as CumSales
RESIDENT tesT
where Status='Rejected'
group by Date,ID,Status
order by ID, Date;

NoConcatenate
test3:
RESIDENT test2
order by Date, ID;

drop tables test2;

In Expression:

Count1 = count({<Status={"Approved"}, Sales={">=45"}>}DISTINCT ID)

Count2= count({<Status={"Approved"}, CumSales={">=45"}>}DISTINCT ID)

Hope this works for you!

• ###### Re: How to distinct count cumulative with date ranges

Hi bro,

I've tried to modified as your instruction, but, pardon, I am a newbie and several things I havent understood:

only(Sales) as Sales,
if(ID=peek('ID'),if(peek('CumSales')<45,peek('CumSales')+only(Sales),0),only(Sales)) as CumSales

How do ONLY and PEEK work? And we havent got CumSales field, right? And cound you please help me with the full script for this app?

Bwt, I attached my data and app also sothat you may look at them. Thank you.

• ###### Re: How to distinct count cumulative with date ranges

Sorry, I dont have qlik sense - but this solution should work for your case.
// Load data from source file
tesT:
@2 as Date,
@3 as Sales,
@4 as Status
FROM
test.txt
(
txt, codepage is 1252, no labels, delimiter is spaces, msq, header is 1 lines);

/* Now, load those records which have status as approved and order it by ID and Date
We are also grouping them by Date, ID and Status to get aggregations work for CumSales

Here we are doing 2 things:

Sales Column - Just stores the actual sales data - we are using the only aggregation to rule out the error that would otherwise be thrown for not including this column in the group by fields.

CumSales Column - This field is not actually storing cumulative sum of sales but is just an identifier to see by what date a particular ID has reached the threshold of 45.
It uses peek to compare the current record value with the previously loaded record.

Here we are comparing if current ID is same as previous ID and also,
if the value under CumSales for that ID has not reached the threshold of 45 before loading this current record.
Only then it will sum up the current sales with previous value under CumSales column and add updated value to CumSales column. Otherwise, it will default to zero.

We are doing this to use CumSales as a field during our front end calculation for count2 so that we can filter - Sales>=45 and get the count

*/

NoConcatenate
test2:
Date,ID,Status,
only(Sales) as Sales,
if(ID=peek('ID'),if(peek('CumSales')<45,peek('CumSales')+only(Sales),0),only(Sales)) as CumSales
RESIDENT tesT
where Status='Approved'
group by Date,ID,Status
order by ID, Date;

// Just concatenating the records in status Rejected also - just to get all records into final table

Concatenate(test2)
Date,ID,Status,
only(Sales) as Sales,
only(Sales) as CumSales
RESIDENT tesT
where Status='Rejected'
group by Date,ID,Status
order by ID, Date;

// Reloading the data for ordering by Date and ID - just in case if needed so.
NoConcatenate
test3:
RESIDENT test2
order by Date, ID;

drop tables tesT,test2;

Please let me know if you still have issues in understanding any specific thing.

Or if this solution does not work for you

• ###### Re: How to distinct count cumulative with date ranges

Hi bro,

Your explanation is quite good, I can catch up with that.

We seem to going on the right direction but It hasnt really solved my case.

Below is my result with your suggested count and two other expressions that I modified from yours and learnt from Sunny Talwar in this topic. His expressions seem to be better but also not really solve. Could you please revise that once again, we neally make it.

 Date count({=45"}>}DISTINCT [Outlet ID]) count({=45"}>} DISTINCT [Outlet ID]) Count( {\$<[Outlet ID] = {"=Sum({\$<[Order status]={'Approved'}>} Sales) >= 45"},[Order status]={'Approved'}>} DISTINCT [Outlet ID]) Count( {\$<[Outlet ID] = {"=Sum({\$<[Order status]={'Approved'}>} CumSales) >= 45"},[Order status]={'Approved'}>} DISTINCT [Outlet ID]) 11/1/2017 2 2 2 2 11/4/2017 4 4 3 3 11/5/2017 2 2 1 1 11/6/2017 1 1 1 1 11/7/2017 2 2 1 1
• ###### Re: How to distinct count cumulative with date ranges

Hi bro,

Although I got correct answer at the below section of this thread by Sunny Talwar, the solution seem to be not applicable for high volume data as it takes too long to reload and calculation timed out error on sheet.

I still hope your direction solve my problem and performance is good. Could you please keep an eye on this and work out the best way?

Thank you so much.

• ###### Re: How to distinct count cumulative with date ranges

Hung I wouldn't close the thread if you don't have an optimum solution. May be someone else have better ideas, especially swuehl might be able to contribute in some way.

Best,

Sunny

• ###### Re: How to distinct count cumulative with date ranges

May be using The As-Of Table approach

Table:

Outlet ID, Date, Sales, Order status

111, 11/1/2017, 10, Approved

112, 11/1/2017, 20, Rejected

113, 11/1/2017, 30, Rejected

111, 11/1/2017, 90, Rejected

118, 11/1/2017, 120, Approved

111, 11/4/2017, 40, Approved

113, 11/4/2017, 60, Approved

115, 11/4/2017, 100, Approved

117, 11/4/2017, 110, Rejected

119, 11/4/2017, 130, Rejected

112, 11/5/2017, 50, Rejected

114, 11/5/2017, 70, Approved

115, 11/6/2017, 80, Approved

112, 11/7/2017, 50, Approved

114, 11/7/2017, 70, Rejected

];

MinDate:

Resident Table;

LET vMinDate = Peek('MinDate');

DROP Table MinDate;

AsOfTable:

Date(Date - IterNo() + 1) as Date

Resident Table

While Date - IterNo() + 1 >= \$(vMinDate);

• ###### Re: How to distinct count cumulative with date ranges

Hi bro,

I've tried to open your attachment but QV failed to load file. I also followed this topic but it does not work.

https://community.qlik.com/message/103409#103409

I did then copy ur script but it seems to be incorect I will try again and report later. Thank you.

• ###### Re: How to distinct count cumulative with date ranges

Here is qvf sample attached

• ###### Re: How to distinct count cumulative with date ranges

Thank you, Sunny but it is not really what I meant.

For example, by 11/4/17 we should have 4 outlets that sales >= 45 with status = "approved" like my filter below: (outlet 111 has cumulative sales = 50):

 Outlet ID Date Sales Order status 111 11/1/2017 10 Approved 118 11/1/2017 120 Approved 111 11/4/2017 40 Approved 113 11/4/2017 60 Approved 115 11/4/2017 100 Approved

• ###### Re: How to distinct count cumulative with date ranges

May be this:

Count(DISTINCT {<[Order status] = {‘Approved’}, [Outlet ID] = {“=Sum({<[Order status] = {‘Approved’}>} Sales) >= 45”}>} [Outlet ID])

• ###### Re: How to distinct count cumulative with date ranges

It's still incorrect. I (had to) modify the expression like below and here's the result: Notice that on the first day only 1 outlet with sales > 45, status = approved.

 AsOfDate Count( {\$<[Outlet ID] = {"=Sum({\$<[Order status]={'Approved'}>} Sales) >= 45"},[Order status]={'Approved'}>} DISTINCT [Outlet ID]) 11/1/2017 2 11/4/2017 4 11/5/2017 5 11/6/2017 5 11/7/2017 6
• ###### Re: How to distinct count cumulative with date ranges

It seemed you were looking at one day at a time. But it seems you are not... I am not home right nowto test it out, but will try it once I am home unless someone else is able to offer help before I do.

• ###### Re: How to distinct count cumulative with date ranges

Count(DISTINCT {<[Order status] = {‘Approved’}, [Outlet ID] = {“=Sum({<[Order status] = {‘Approved’}>} Sales) >= 45”}>} [Outlet ID])

result in this table:

 AsOfDate Count(DISTINCT {<[Order status] = {‘Approved’}, [Outlet ID] = {"=Sum({<[Order status] = {‘Approved’}>} Sales) >= 45"}>} [Outlet ID]) - 0
• ###### Re: How to distinct count cumulative with date ranges

Like I said, I am not home... will check once I am home.

• ###### Re: How to distinct count cumulative with date ranges

May be try this

Count(DISTINCT {<[Order status] = {'Approved'}>} Aggr(If(Sum({<[Order status] = {'Approved'}>} Sales) >= 45, [Outlet ID]), [Outlet ID], AsOfDate))

• ###### Re: How to distinct count cumulative with date ranges

Hi Sunny,

Your solution is correct. Thank you so much.

However, this way could be suitable for low volume data only. I did try with my sales data from Jan-2017 up to now and it takes 1hour and 14 mins to reload data due to AsOfTable; without it, Qlik only takes around 4 mins to reload. My latop is rather good with 8Gb ram and Core i7 chipset. In app sheet, I faced calculation timed out error on running this expression.

Is there any way to improve that?

• ###### Re: How to distinct count cumulative with date ranges

How far do you plan to accumulate? for a month? for a year? accumulate without end? It depends on what you need finally, because I don't think there is a front end solution (or at least it is not going to be a simple or efficient one). But you can def. try to improve performance of AsOfTable if you don't have to do the while loop till the very end.

• ###### Re: How to distinct count cumulative with date ranges

I want to calculate by week and my sales data for this year has reached around 4mil rows. Perhaps you're correct, QS hasnt provided any front-end solution for this complicated case.