# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## How to distinct count cumulative with date ranges

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.

Tags (1)
1 Solution

Accepted Solutions
MVP

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

22 Replies
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.

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.

New Contributor III

## Re: How to count cumulative with date ranges

Can anyone help me...

New Contributor III

## Re: How to distinct count cumulative with date ranges

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

MVP

## Re: How to distinct count cumulative with date ranges

May be using The As-Of Table approach

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

];

MinDate:

Resident Table;

LET vMinDate = Peek('MinDate');

DROP Table MinDate;

AsOfTable:

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

Resident Table

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

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.

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.

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

MVP

## Re: How to distinct count cumulative with date ranges

Here is qvf sample attached