Fetch max date from selected dates of a given quarter

[#1]

Hi,

I have a date field with an alternate state. As the data in the qvd comes every fortnight, dates that are available in the [Date ] field includes for example, 01/15/2017,01/31/2017,02/15/2017,02/28/2017 so on. These dates are available in a multibox object for the users to filter. However, as per business requirement date is restricted to appear inside current selections.

Requirement:

Financial year starts from September and ends in August.

E.g. Q1 : Sept 2016 to Nov 2016, Q2 : Dec 2016 to Feb 2017 and so on.

I want to display quarter to date trend in the chart. That is when user selects one date from Q1 say, 11/15//2016 then show data from start of that quarter till selected date. If user selects 12/15/2016, 12/31/2016 and 01/15/2017 from Q2, then display QTD data for  01/15/2017. In other words, I want to fetch the maximum date of the dates selected in any given quarter. If only one date is selected from each of the four quarters then those will be the max dates of respective quarters.

Challenge:

As alternate state is used for the [Date ] field, I am not able to use the Getfieldselections in the chart expression, to fetch the max of selected dates.

Hence, I am using following expression to fetch the dates that will be selected from multibox filter:

aggr(only({TrendDate}[Date ]),[Date ]) .

Here, TrendDate is the name of alternate state and [Date ] is the field.

This expression would return the dates that are selected from the multibox.

That is, if the user selects one date from each quarter, say 10/15/2016, 01/15/2017, 04/15/2017, 07/15/2017 , then ideally there should be four quarters Q1,Q2,Q3 and Q4 displayed in the chart, as these dates fall in between each quarter. I am trying to make use of set analysis to build this logic to return the maximum date from the selected ones, falling between QuarterStart and QuarterEnd.

Please help me get the right syntax of set analysis. Any other suggestions are welcome.

Thanks

[#2]

Hi Sunny,

One correction:

Please remove "=" from the expression of vMaxProductivityDate variable.

[sunny_talwar]

I didn't get a chance to look at this, but will try to check it today

[#4]

Hi Sunny,

I have not removed '=' from vMaxProductivityDate variable.

However, there is a change in the chart expression as below:

=  IF($(vTrendByMetricDateType)=1,Date([Date ],'DD-MMM-yy')
,IF($(vTrendByMetricDateType)=2,IF($(vTrendByMetricDateType)=2 and Day([Date ])<>15 , Date([Date ],'MMM-yy')
,IF($(vTrendByMetricDateType)=2 and [Date ]= Date('$(vMaxProductivityDate)'),Date([Date ],'MMM-yy')))
,IF($(vTrendByMetricDateType)=3,IF(Match(Date(QuarterEnd([Date ],0,9),'MM/DD/YYYY'),Date([Date ],'MM/DD/YYYY')),$(vQuarterNameofDate)
,IF($(vTrendByMetricDateType)=3 and [Date ]= Date('$(vMaxProductivityDate)'), $(vQuarterNameofDate)))
)
)
)

Please note, I have used the variable in this manner Date('$(vMaxProductivityDate)').

Thanks.