7 Replies Latest reply on Oct 22, 2018 8:50 AM by Mike Ann

# Comparing Dates Calculation -- difference

I want to select multiple records and compare it with another set of records from same dimension and find the difference

like the ones highlighted red - the ones that are black . or any other variation.

is it possible

 YYYYMM YYYY MM Sales 2017-01 2017 01 1792 2017-02 2017 02 1202 2017-03 2017 03 1941 2017-04 2017 04 2960 2017-05 2017 05 1674 2017-06 2017 06 548
• ###### 1. Re: Comparing Dates Calculation -- difference

I think the answer is sort of!

In the attached I've first created a Global Filter, so you can filter to all the dates to be compared (it's important that this is a context filter). Next is just the yyyymm field where everything ticked will be selected and everything not ticked will be comparison

We then use this formula to get the comparisons

[Comparison Sales]

SUM({FIXED: SUM([Sales])})

-

SUM([Selected Sales])

This works as FIXED LoDs are computed before any regular filters, so the FIXED LoD gives all (in context) sales, and takes away the selected sales (which as a regular aggregate, SUM, is affected by LoDs)

Hope that helps and makes sense.

• ###### 2. Re: Comparing Dates Calculation -- difference

You mean, like below ..?

Attached work book for your reference.

Step-1

Step-2

Ste-03

• ###### 3. Re: Comparing Dates Calculation -- difference

I am trying to have ability to select maybe month 1,2,3 and subtract from 4,5,6 .

not [1,2,3,4,5,6]-[1

• ###### 4. Re: Comparing Dates Calculation -- difference

So if I set it up so we have the first 3 months selected it looks like this

and this reflects this

As you said you wanted to compare any month combinations this method lets you compare everything that is selected with everything that isn't. If that's not what you wanted, let me know how you want it to work and I'll take a look

• ###### 5. Re: Comparing Dates Calculation -- difference

What you presented is an option .

what if we selected only 2 months and compared with 2 months and didn't select the others

[1,2]-[3,4] didn't select [5,6]

I am only posting very small part of the monthyyy data. the actual data contains data from 2014 till now

• ###### 6. Re: Comparing Dates Calculation -- difference

hi Mike,

So we use the Global Filter to restrict the data down to the set of dates (both selected and comparison) and then by either ticking (or unticking) tells it how we want to construct the 2 groups to compare

If we "know" how the comparison would work (eg. If I select 2017-04 I always want to compare it to 2016-04) then we don't need to use this (slightly convoluted) double filter, but this way lets you compare anything to anything (and is dynamic, as in you won't need to change the parameter list of values when the data updates, if we'd gone down that route)

• ###### 7. Re: Comparing Dates Calculation -- difference

Hey Thank you, it works.