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

[varunreddy]

Hi All,

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 someone please help?

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

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

Cheers,

Varun

[sunny_talwar]

Check my response here

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

[varunreddy]

It actually didn't work.

[sunny_talwar]

Share a sample if you can to show what you have (in addition to your dimension)

[CarlosAMonroy]

Hi Varun,

You can try the following expression:

count(DISTINCT {< Date= {">=$(vMinDate) <=$(vMaxDate) "} >}Rule Name)

Where:

vMinDate =      =Date(AddMonths(date(today()),-6))

vMaxDate =     =Date(today())

Hope that helps,

Carlos M

[varunreddy]

Hi Carlos,

It didn't work.

Dimensions used:

Date

GB/GF Name

Expression Used:

count({<Date= {">=$(vPrevious5MonthYear) <=$(vCurMonthYear)"} >}[DQ Rule Name])

vCurMonthYear =  =date(max(Date),'MMM YYYY')

vPrevious5MonthYear = =Date(AddMonths(max(Date),-5),'MMM YYYY') .

Can you please see, if I am missing anything?

I want to see Current 6 months in the line chart by default, If I select any date, I want to see 6 months from the date selected.

[sunny_talwar]

Not sure if this will change anything, but can you remove space between the end of variable name and <= (like below)

Count({<Date= {">=$(vPrevious5MonthYear)<=$(vCurMonthYear)"}>}[DQ Rule Name])

[varunreddy]

Tried. No luck. Will see if I can mock up something and share.

Now I see this:

[CarlosAMonroy]

Hey Varun,

Does the Date field have JAN 2018 as format?

Do you have any Date field using regular date format (MM/DD/YYYY).

I guess the issue is not the expression but the date format.

Try using different date fields.

Carlos M

[varunreddy]

When I try to compute using any one variable, it works. i.e.count( {< Date= {'$(vPrevious5MonthYear)'} >}[DQ Rule Name]).

It is not working only when we are using both.

When I try max(Date), I am getting number. So I am trying to convert using Date format i.e. Date(Date,'MMM YYYY')