Set analysis for subtracting multiple date ranges.

[Anonymous]

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!

Thanks in advanced. 

[petter]

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

should give you what you want.

[thi_pham]

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

[Anonymous]

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]

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]

Up for helping.

[thi_pham]

could you share the qvf and your expectation?

[Anonymous]

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.

Thank you in advance.

[thi_pham]

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

[Anonymous]

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.