Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analysis for subtracting multiple date ranges.

Hi there,

There is a date range problem for me. I have selected multiple dates in the filter panel showing a table below:

  

Datesum(Cost)above(Date)
02/01/20185,745.0-
31/01/20183,829.002/01/2018
23/02/20181,875.031/01/2018
16/03/20182,392.023/02/2018
28/04/20182,870.016/03/2018
10/05/20187,734.428/04/2018
16/05/2018657.610/05/2018
01/06/2018531.716/05/2018
05/07/20188,245.201/06/2018
12/07/2018603.305/07/2018

Im tending to create a measure to subtract Gap form the selected Date and the previous selected date (ex: Cost in 12/07 - Cost 05/07,...) and the result is for the greater date as:

   

DateCostabove(Date)Gap
02/01/20185,745.0--1,916.0
31/01/20183,829.002/01/2018-1,954.0
23/02/20181,875.031/01/2018517.0
16/03/20182,392.023/02/2018478.0
28/04/20182,870.016/03/20184,864.4
10/05/20187,734.428/04/2018-7,076.8
16/05/2018657.610/05/2018-125.9
01/06/2018531.716/05/20187,713.5
05/07/20188,245.201/06/2018-7,641.9
12/07/2018603.305/07/2018-603.3

my set analysis is

sum({<Date=p(Date),>} Cost)

-

sum({<Date={"$=(above(Date))"}>} Cost)

but it didnot work.

Could anyone enlighten me!

Thanks in advanced. 

14 Replies
petter
Partner - Champion III
Partner - Champion III

Sum(Cost)-Above(Sum(Cost))

should give you what you want.

thi_pham
Creator III
Creator III

I think it should be: below(sum(cost)) - sum(cost)

Anonymous
Not applicable
Author

Thank you for your helping very much, it works in the other way. However, i really want to set some thing like date - above(date). Because when i add 1 dimension to separate the cost of listed day by customer, i cant use the fuction like:

if(aggr(sum(Cost)-above(sum(Cost)),CustID)<0,'Good','Not Good')

Would you mind to help me a little more.

Anonymous
Not applicable
Author

Thank you very much. Like i reply to sir Skjolden above, i want to use day and above day.

Could you help me a bit.

Anonymous
Not applicable
Author

Up for helping.

thi_pham
Creator III
Creator III

could you share the qvf and your expectation?

Anonymous
Not applicable
Author

Hi. Thank you.

Unfortunately, It is unable for me to share qvf. I just want to add one more measure to the original example described what is filter whether customer buy more than the last previous day or not and how much:

Original table

DateCostabove(Date)Gap
23/02/20181,875.031/01/2018517.0
16/03/20182,392.023/02/2018478.0
28/04/20182,870.016/03/20184,864.4

...

Expected table

DateCostabove(Date)Status
23/02/2018-30031/01/2018Less
23/02/201821731/01/2018More
16/03/2018-100023/02/2018Less
16/03/2018147823/02/2018More
28/04/2018-20016/03/2018Less
28/04/20185064.416/03/2018More
...

Yet i had fixed a specific day to filter Less or More for customer as:

     =if(aggr(sum({<Date={'28/04/2018'},>} Cost) -

     sum({<Date={'16/03/2018'}>} Cost),CustID)<0,'Less',More)

and the amount is:

     sum({<backup_date={'28/04/2018'}>} Cost)/1e6

     -

     sum({<backup_date={'16/03/2018'},>} Cost)/1e6

By this way, i have to create too much table for each couple day while i prefer adding many days to one table.

Using Below or Above without days cant help filtering.

Could you suggest a solution.

Thank you in advance.

thi_pham
Creator III
Creator III

Do you want the separate the cost column by sum of negative & positive values?

Anonymous
Not applicable
Author

To be exactly, it is the total amount of customer cost which is in 28/04/2018 "Less" or "More" than in 16/03/2018 and many previous days.

=if(aggr(sum({<Date={'28/04/2018'},>} Cost) -

     sum({<Date={'16/03/2018'}>} Cost),CustID)<0,'Less',More).

Thank you a lot.