10 Replies Latest reply on Nov 30, 2016 5:55 AM by Faris Ahamad M

# To display comparative charts for measures having values on two specific dates

I have a table which is being updated on a daily basis.But not all the measures will be updated daily some will be updated may be once in 3 days . I need to display a comaparative chart for the Dimension which have values for two specific dates.ie i need to display that only if have values for both the dates, .Anyone can help on this please? Thanks in Advance

• ###### 1. Re: To display comparative charts for measures having values on two specific dates

hi Faris,

Can post some dummy data (just a few rows, with some measures for both days, and some for just one). It'll be easier for me to give you an exact solution (for example do you have NULL rows where the measure is missing or just no rows at all).

• ###### 2. Re: To display comparative charts for measures having values on two specific dates

Faris,

Find my approach below and stored in attached workbook version 9.3

Measure 1:

if {fixed [date (Exact)]:sum([measure1])}>=0

and {fixed [date (Exact)]:sum([measure2])}>=0

then [measure1] end

Measure 2:

if {fixed [date (Exact)]:sum([measure1])}>=0

and {fixed [date (Exact)]:sum([measure2])}>=0

then [measure2] end • ###### 3. Re: To display comparative charts for measures having values on two specific dates i want to make a comparative chart of the duration for each program which has values on say, both 18/11/2016 amd 19/11/2016

• ###### 4. Re: To display comparative charts for measures having values on two specific dates

hi Faris,

So looks like your data might have more than the 2 days you want to compare? as you've asked in this example on the 18th and 19th (where the 18th is the MIN date in the data I'm going to go from there...you can adapt this to your exact needs). I've also split the elements of the calculations into separate fields for explanatory purposes, but you can nest these into a single calculation (or not...it's up to you!).

So to get the MIN date in the data...we can use an LOD

[Min Day of Data]

DATETRUNC('day',{MIN([Date and Time of Start])})

I can then look at this date, and the next date (19th) and see how many days of data each [Program Name] has.

{FIXED [Program Name]: COUNTD(IIF([Date and Time of Start] = [Min Day of Data] OR [Date and Time of Start] = [Date and Time of Start] = [Min Day of Data] +1,[Date and Time of Start],NULL))} = 2

NB. As I've not used the Calculation Editor I can't guarantee I have all the brackets in the right place!!

You can then bring this field onto the filter shelf and set to true. This means we'll only have [Program Names] returned where these is an entry for both dates.

Hope that does the job, and makes sense, but let me know if not.

• ###### 5. Re: To display comparative charts for measures having values on two specific dates

Simon,

Can you explain the logic used? it's not working for me !!

• ###### 6. Re: To display comparative charts for measures having values on two specific dates

hi Faris,

So attached is a worked up example...and here is the table As you can see only program name B & D are true, as these are the only ones with data for 18th and 19th.

So the [Min Day of Data] formula

DATETRUNC('day',{MIN([Date and Time of Start])})

This returns the first (MIN) day over the whole data source. This is a FIXED LoD, but as we haven't specified a dimension, it is run over all data.

Let's break the next one down

{FIXED [Program Name]: COUNTD(IIF([Date and Time of Start] = [Min Day of Data] OR [Date and Time of Start] = [Date and Time of Start] = [Min Day of Data] +1,[Date and Time of Start],NULL))} = 2

IIF([Date and Time of Start] = [Min Day of Data] OR [Date and Time of Start] = [Date and Time of Start] = [Min Day of Data] +1,[Date and Time of Start],NULL))

Does the Date and Time Start equal either the MIN Date (18th) or the next day ([Min Day of Data] +1) and if so return the [Date and Time of Start] else return NULL

Then the LoD part...take this return and do a COUNTD on the [Date and Time of Start] (NULLs don't get counted in a COUNTD)....now run and return this COUNTD for each [Program Name]

Just thinking about it...you may also need to TRUNC the [Date and Time of Start] (in all the formulas) to 'day', as you have both day and time, and you only want to assess it at day level

Hope that makes a bit more sense.

• ###### 7. Re: To display comparative charts for measures having values on two specific dates

Simon,

IIF([Date and Time of Start] = [Min Day of Data] OR [Date and Time of Start] = [Date and Time of Start] = [Min Day of Data] +1,[Date and Time of Start],NULL))

is this statement correct? i removed one [Date and Time of Start] and tried.but it's showing the entries which has values on either of the dates. i tried with and. then there is no result.

• ###### 8. Re: To display comparative charts for measures having values on two specific dates

Sorry..my bad (the typo strikes again!!)

it should be

IIF([Date and Time of Start] = [Min Day of Data] OR [Date and Time of Start] = [Min Day of Data] +1,[Date and Time of Start],NULL))

So yes this formula (one correct!) will return a date for either...but then we do a COUNTD of this against each [Program Name] and then test if this countd = 2 (meaning they have to have an entry for both dates)

1 of 1 people found this helpful
• ###### 9. Re: To display comparative charts for measures having values on two specific dates

...Attached in an Excel spreadsheet, showing (as best as I can show in Excel given the limited functionality!) what each calculation is doing.

Hopefully, that helps with what's going on here

• ###### 10. Re: To display comparative charts for measures having values on two specific dates

Thank you Simon It worked!!