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