How to show 6 months data using the set analysis in the dimension

[varunreddy]

Hi Guys,

I am trying to show 6 months data on the report by writing set analysis using the "Add Calculated Dimension". Can someone please help me with the expression in the dimension or suggest any other option?

Current Date -  Dec 2017. I want to show all dates from June. If I select November 2017, I would like to see all the dates from May 2017.

Expression I have been trying:

=Aggr(Only({$<Date={">=$(=date(max(Date),'MMM YYYY'))<=$(=Date(AddMonths(max(Date),-6),'MMM YYYY'))"} >} Date),Date)

Thanks in advance!

Cheers,

Varun

[sunny_talwar]

May be try with 1 instead of $

=Aggr(Only({1<Date={">=$(=date(max(Date),'MMM YYYY'))<=$(=Date(AddMonths(max(Date),-6),'MMM YYYY'))"} >} Date), Date)

[varunreddy]

Hi Sunny,

This is not working.

I also tried another method. I am adding each month in the expression.

Dimension - GB/GF Name

Current Month Expression - aggr(Count({<Date = {'$(vCurMonthYear)'}, [Key Business Driver] = {'BCBS239'}>} [DQ Rule Name]),[GB/GF Name])

Previous Month expression - aggr(Count({<Date = {"=Date(AddMonths(max(Date),-1),'MMM YYYY')"}>}[DQ Rule Name]),[GB/GF Name])

Previous Month expression - aggr(Count({<Date = {"=Date(AddMonths(max(Date),-2),'MMM YYYY')"}>}[DQ Rule Name]),[GB/GF Name])

This is also not working. Not sure I am missing something. Can you please help?

[sunny_talwar]

Why are you using Aggr() function if you have the same as your dimension

Try this

Dimension

[GB/GF Name]

Expressions

Count({<Date = {'$(vCurMonthYear)'}, [Key Business Driver] = {'BCBS239'}>} [DQ Rule Name])

Count({<Date = {"=Date(AddMonths(max(Date),-1),'MMM YYYY')"}>}[DQ Rule Name])

Count({<Date = {"=Date(AddMonths(max(Date),-2),'MMM YYYY')"}>}[DQ Rule Name])

[varunreddy]

Oh Yeah! Thanks for correcting. I tried it but I am seeing the same numbers for all the months, but this is not true

[sunny_talwar]

I am not seeing anything at all lol... I mean can you share a sample or image of what you are seeing... I won't know what you have until you show me something

[varunreddy]

It worked.

I created the 6 separate variables  and calculated the dates. I referred those variables in the expression instead of calculating in the set analysis.

I am not sure why , but it is working.

My question here is performance wise, is it good to use it using the variable or is it better calculating in the set analysis?

Thanks for your help

Cheers,

Varun

[sunny_talwar]

Should be the same thing ...

[varunreddy]

Cool

Thanks,

Varun

[varunreddy]

Hi Sunny,

I am trying to create line chart. Two dimensions and one expression.

Dimensions:

Date and GB/GF

Expression

Count(Rule Name)

This is working fine.

Now I am trying to show only 6 months of data out of all dates available. I am using below expression in the dimension to display 6 months data but I am not seeing the dates. Can you please help?

=Aggr(Only({<Date = {'>= $(=vPrevious5MonthYear) <= $(=vCurMonthYear)'}>}Date),Date)

Below is the graph. I don't see Dates.