14 Replies Latest reply: Jul 18, 2018 5:16 AM by Rangam Seshadri

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

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

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

should give you what you want.

• ###### 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.

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

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

• ###### 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.

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

Up for helping.

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

could you share the qvf and your expectation?

• ###### 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.

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

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

• ###### 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.

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

It's quite difficult to imagine what you are going to do.

If could, please provide a sample qvf with some samples of related fields.

For the dimension you want to add, if you have no idea how to make it on chart, I suggest to pre-specify it on data-load, then everything will become easier.

As you mentioned, you fixed a specific date to determine value of new dimension, I suggest to put the reference value to a fixed variable (by add equal character '=' before expression in variable).

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

Im so sorry for not preparing example carefully. Let me explain again:

The Table of Customer:

 Date CustID Cost 23/02 A 100 23/02 A 200 23/02 B 500 16/03 C 10 16/03 A 50 16/03 B 1000 28/04 A 500 28/04 B 200 28/04 C 100

For the status, im using the measure:

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

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

For Gap(1) is:

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

-

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

Which can only show only 2 dates while i want some selected date to show the expected table above to check whether customer buy more or less following by the date:

 Date CustID Sum Cost Gap(1) Status 23/02 A 300 16/03 A 50 -250 Less 28/04 A 500 450 More 23/02 B 500 16/03 B 1000 500 More 28/04 B 200 -800 Less 16/03 C 10 28/04 C 100 90 More

Then, I just want to minisize the table below only by date showing the amount for the final result :

 Date Gap Status 23/02 810 16/03 -250 Less 16/03 500 More 28/04 -800 Less 28/04 540 More

Using Below or Above like you can only show:

 Date Sum Cost Gap 23/02 810 16/03 1060 250 28/04 800 -260

The question is how i can create a measure that subtract from a punch of selected days.

Thank you a lot!

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

The idea is adding new column in data load to store the cost variance of each account comparing the previous day.

Hope it help.

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

You are once of a kind. However, i have to ask my manager to access into the data loader so i prefer something like function measure as set analysis for this situation.

Thank you again.

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

if(RangeSum(aggr(sum(Cost)-above(Sum(Cost),1),CustID))<0,'Less','More')

Try this, but it will not give you an expected results bcz Each Customer ID has different Status.