topic Re: Select a date range in Qlikview? in QlikView

Select a date range in Qlikview?

gauravgg — Sat, 13 May 2017 18:46:24 GMT

I have the following expression

sum({<DocType={'A'}>} Value1)*-1 + sum({<DocType={'A'}>} Value2)

I have Data from Jan 2013

I have Date field in my table

I have to show the above expression in KPI

I have given the user to select Month,Year,Quarter

What i want is

for example :- I the user selects Year=2016 and Quarter =Q1 and Month =Feb

So the value in KPI should show Value for the date range form 1 Jan 2013 to 28 Feb 2016

Note:- i do not have access to the data load editor

Thanks in advance

Re: Select a date range in Qlikview?

Peter_Cammaert — Sun, 14 May 2017 17:16:13 GMT

You mean that you want want to aggregate data in a QuarterToDate timeline where ToDate is the last day of the selected month (or the most recent month if your calendar allows multiple selecitons)?

Let's assume that you have a calendar with a field CalendarDate, then you can try adding someting like this to your set expression (in both sum() calculations):

{ ... CalendarDate={'>=$(=QuarterStart(Max(CalendarDate)))<=$(=MonthEnd(Max(CalendarDate)))'}, ...}

Re: Select a date range in Qlikview?

swuehl — Sun, 14 May 2017 17:22:09 GMT

Two additions:

1) It seems you want to only filter the date range up to the selected month end (i.e. don't define the lower limit of the range). Hence just remove the lower limit from the numeric search.

2) If the user can make active selections in calendar fields, like Month, Year, Quarter, you need to ignore selections in these fields to avoid incompatible sets (if you are using set identifier $):

{$< CalendarDate={"<=$(=MonthEnd(Max(CalendarDate)))"}, Year=, Quarter=, Month=> }