# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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:

 Date sum(Cost) above(Date) 02/01/2018 5,745.0 - 31/01/2018 3,829.0 02/01/2018 23/02/2018 1,875.0 31/01/2018 16/03/2018 2,392.0 23/02/2018 28/04/2018 2,870.0 16/03/2018 10/05/2018 7,734.4 28/04/2018 16/05/2018 657.6 10/05/2018 01/06/2018 531.7 16/05/2018 05/07/2018 8,245.2 01/06/2018 12/07/2018 603.3 05/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:

 Date Cost above(Date) Gap 02/01/2018 5,745.0 - -1,916.0 31/01/2018 3,829.0 02/01/2018 -1,954.0 23/02/2018 1,875.0 31/01/2018 517.0 16/03/2018 2,392.0 23/02/2018 478.0 28/04/2018 2,870.0 16/03/2018 4,864.4 10/05/2018 7,734.4 28/04/2018 -7,076.8 16/05/2018 657.6 10/05/2018 -125.9 01/06/2018 531.7 16/05/2018 7,713.5 05/07/2018 8,245.2 01/06/2018 -7,641.9 12/07/2018 603.3 05/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!

14 Replies
MVP

## Re: Set analysis for subtracting multiple date ranges.

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

should give you what you want.

Contributor

## Re: Set analysis for subtracting multiple date ranges.

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

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.

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.

New Contributor II

Up for helping.

Contributor

## Re: Set analysis for subtracting multiple date ranges.

could you share the qvf and your expectation?

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

 Date Cost above(Date) Gap 23/02/2018 1,875.0 31/01/2018 517.0 16/03/2018 2,392.0 23/02/2018 478.0 28/04/2018 2,870.0 16/03/2018 4,864.4

...

Expected table

 Date Cost above(Date) Status 23/02/2018 -300 31/01/2018 Less 23/02/2018 217 31/01/2018 More 16/03/2018 -1000 23/02/2018 Less 16/03/2018 1478 23/02/2018 More 28/04/2018 -200 16/03/2018 Less 28/04/2018 5064.4 16/03/2018 More ...

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.

Contributor

## Re: Set analysis for subtracting multiple date ranges.

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

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.