Qlik Community

Qlik GeoAnalytics Discussions

Discussion Board for collaboration regarding Qlik GeoAnalytics.

Not applicable

Fetch max date from selected dates of a given quarter

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Fetch max date from selected dates of a given quarter

Would you be able to share some test data to test this out?

12 Replies
MVP
MVP

Re: Fetch max date from selected dates of a given quarter

Would you be able to share some test data to test this out?

Not applicable

Re: Fetch max date from selected dates of a given quarter

Hi Sunny,

Attached is one test qvw reloaded with test data for two quarters, viz. FY17 Q1 (from Sept-16 to Nov-16) and FY17 Q2 (from Dec-16 to Feb-17). The [Date] field includes fortnightly dates, e.g. 09/15/2016,09/30/2016 and so on, as the data gets refreshed every fortnight.

Currently, the chart is displaying QTD data only when I select the quarter end dates, that is 30-Nov-16 for Q1 and 28-Feb-17 for Q2.

What I am trying to achieve is for example,

If I select any two dates in the middle of Q1, say 10/15/2016 and 10/31/2016, and 01/15/2017 and 01/31/2017 of Q2, then I should be able to see two quarters in the chart. However, the data for maximum date selected in a given quarter will be displayed on chart, which is 10/31/2016 and 01/31/2017 in this case.

Please help me get the right logic here.

Let me know in case you need any further details.

Thanks!!

Not applicable

Re: Fetch max date from selected dates of a given quarter

Attaching the excel with test data.

MVP
MVP

Re: Fetch max date from selected dates of a given quarter

Try this:

Dimension

QuarterName

Expression

FirstSortedValue([Percent Metric 1], -Date)

Capture.PNG

Not applicable

Re: Fetch max date from selected dates of a given quarter

Hi Sunny,

I tried using FirstSortedValue to one of the expressions in the chart in the below manner :

FirstSortedValue($(vCurr_Chargeable_Per_Trend), -aggr(only({TrendDate}[Date ]),[Date ]))

(vCurr_Chargeable_Per_Trend) - This variable has following expression and percent number format is given in the chart:

SUM({<Current_Flag = {1}, [Date ] = TrendDate::[Date ], [~Date Type] = TrendDate::[~Date Type] >}[Metric A])/
SUM({<Current_Flag = {1}, [Date ] = TrendDate::[Date ], [~Date Type] = TrendDate::[~Date Type] >}[Metric B])

I tried using this expression aggr(only({TrendDate}[Date ]),[Date ])) in a listbox to check if I am getting all the selected dates, and it does.

I am using the same expression in the chart expression, however it does not seem to be working. The chart shows No data display.

Please suggest.

Thanks!!

MVP
MVP

Re: Fetch max date from selected dates of a given quarter

Since you variable is an expression also, you will need to use another Aggr() function here.. try something like this

FirstSortedValue(Aggr($(vCurr_Chargeable_Per_Trend), [Date ]), -aggr(only({TrendDate}[Date ]),[Date ]))

I am not 100% confident if this will work or not because I am not sure which all dimensions you need in your new Aggr() function... but if this doesn't work, I would suggest you to provide an updates sample where we can exactly see what you are trying to do

Not applicable

Re: Fetch max date from selected dates of a given quarter

Hi Sunny,

While I tried using your earlier suggested solution, it did not work.

I am attaching an updated sample qvw file and the excel used as a data source. It now includes sample data for two quarters i.e Mar-16 to May-16 which is Q3 and Jun-16 to Aug-16 which is Q4.

I have created a variable to define the metric expression and using the same in the chart. Also, in the dimensions, I have added an expression which gets evaluated based on the DateType selected. You would see that I have added DateType , PTD,MTD and QTD having an action set on its textbox.

Note:

1. I have used alternate state in this qvw.

2. In the Dimensions expression of chart, for QTD evaluation , I need to handle the scenario which we are discussing here, instead of MaxProductivityDate which is currently used.

Please suggest if there is any possible solution.

Thanks.

MVP
MVP

Re: Fetch max date from selected dates of a given quarter

Are you sure you have attached the right file? I don't see any change from the last file attached or am I missing something?

Capture.PNG

Not applicable

Re: Fetch max date from selected dates of a given quarter

Hi Sunny,

Apologies. I mistakenly attached the wrong file.

Here's the right one I am attaching. Please refer to my details posted earlier which relates to this file.

Thanks!!

Not sure if the file got attached rightly, sending again.

Community Browser