How to distinct count cumulative with date ranges

[nguyenviethung]

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.

[nguyenviethung]

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

meanwhile your calculation shows 3. Could you please revise it?

[sunny_talwar]

‌May be this:

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

[nguyenviethung]

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

[sunny_talwar]

‌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.

[nguyenviethung]

Your expression:

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

[sunny_talwar]

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

[nguyenviethung]

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({<Status={"Approved"}, Sales={">=45"}>}DISTINCT [Outlet ID])	count({<Status={"Approved"}, CumSales={">=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

[sunny_talwar · Accepted solution]

May be try this

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

[nguyenviethung]

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?

[nguyenviethung]

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.