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.

[mdmukramali]

Hi,

Can you try :

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

PFA.

[nguyenviethung]

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.

Could you please reconsider it?

[nguyenviethung]

Can anyone help me...

[amarnath_krishn]

Please modify your script in load as:

NoConcatenate
test2:
LOAD
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)
LOAD
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:
LOAD *
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!

[sunny_talwar]

May be using The As-Of Table approach

Table:

LOAD * INLINE [

    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:

LOAD Min(Date) as MinDate

Resident Table;

LET vMinDate = Peek('MinDate');

DROP Table MinDate;

AsOfTable:

LOAD Date as AsOfDate,

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

Resident Table

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

[nguyenviethung]

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.

[nguyenviethung]

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.

[amarnath_krishn]

Sorry, I dont have qlik sense - but this solution should work for your case.
// Load data from source file
tesT:
LOAD @1 as ID,
@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:
LOAD
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)
LOAD
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:
LOAD *
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

[sunny_talwar]

Here is qvf sample attached