Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
May be this:
Count(DISTINCT {<[Order status] = {‘Approved’}, [Outlet ID] = {“=Sum({<[Order status] = {‘Approved’}>} Sales) >= 45”}>} [Outlet ID])
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 |
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.
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 |
Like I said, I am not home... will check once I am home.
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 |
May be try this
Count(DISTINCT {<[Order status] = {'Approved'}>} Aggr(If(Sum({<[Order status] = {'Approved'}>} Sales) >= 45, [Outlet ID]), [Outlet ID], AsOfDate))
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?
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.