Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nguyenviethung
New Contributor III

How to distinct count cumulative with date ranges

Hi experts,

I got data like this table:

Outlet IDDateSalesOrder status
11111/1/201710Approved
11211/1/201720Rejected
11311/1/201730Rejected
11111/1/201790Rejected
11811/1/2017120Approved
11111/4/201740Approved
11311/4/201760Approved
11511/4/2017100Approved
11711/4/2017110Rejected
11911/4/2017130Rejected
11211/5/201750Rejected
11411/5/201770Approved
11511/6/201780Approved
11211/7/201750Approved
11411/7/201770Rejected

I want to count cumulative outlets by date like this:

DateCount distinct Outlet ID with total sales >=45, order status ="approved"Count cumulative Outlet with sales >=45 and order status ="approved"
11/1/201711
11/4/201724
11/5/201715
11/6/201715
11/7/201716
Grand Total66

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.

1 Solution

Accepted Solutions

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))

Capture.PNG

22 Replies
mdmukramali
Valued Contributor II

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.

nguyenviethung
New Contributor III

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.

Could you please reconsider it?

nguyenviethung
New Contributor III

Re: How to count cumulative with date ranges

Can anyone help me...

amarnath_krishn
New Contributor III

Re: How to distinct count cumulative with date ranges

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!

Re: How to distinct count cumulative with date ranges

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);

Capture.PNG

nguyenviethung
New Contributor III

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.

nguyenviethung
New Contributor III

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.

amarnath_krishn
New Contributor III

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:
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

Re: How to distinct count cumulative with date ranges

Here is qvf sample attached

Capture.PNG

Community Browser