Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

phucpv9119
New Contributor II

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

Re: Set analysis for subtracting multiple date ranges.

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

should give you what you want.

thi_pham
Contributor

Re: Set analysis for subtracting multiple date ranges.

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

phucpv9119
New Contributor II

Re: Set analysis for subtracting multiple date ranges.

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.

phucpv9119
New Contributor II

Re: Set analysis for subtracting multiple date ranges.

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.

phucpv9119
New Contributor II

Re: Set analysis for subtracting multiple date ranges.

Up for helping.

thi_pham
Contributor

Re: Set analysis for subtracting multiple date ranges.

could you share the qvf and your expectation?

phucpv9119
New Contributor II

Re: Set analysis for subtracting multiple date ranges.

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
Contributor

Re: Set analysis for subtracting multiple date ranges.

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

phucpv9119
New Contributor II

Re: Set analysis for subtracting multiple date ranges.

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.