Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vanderson009
Contributor

Default Date selection in Set Analysis

In my data model, i have dateds from 01-Jan-2017 to 31-Dec-2020.

Current date is 15 Jul 2018, then i want to display default data using set analysis as current date -6 months and +17 months.

It means from 01-Jan-2018 to 31-Dec-2019.

I can not able to create variable using today() function cause if user select Aug-18 then it should as from 01-Feb-2018 to 31-Jan-2020 and I don't think so i can use max date also cause max date is 31-Dec-2020.

Please suggest if there is any possible option is available.

Thanks,

Villyee

1 Solution

Accepted Solutions
MVP
MVP

Re: Default Date selection in Set Analysis

You have three possible selection scenarios:

1) The user hasn't selected anything and then the current date - today - should be used as reference date.

2) The user has selected a single date and then that date should be the reference date.

3) The user has selected multiple dates and then either the first or the last date should be the reference date.

A) This can be done by creating four variables and using the last variable vRefDateRange in the set expression:

vRefDate:

=Date(If( GetSelectedCount(aDate)=0 , Today(1) , Min(aDate) ) )

vRefDateRangeMin:

=MonthStart(AddMonths(vRefDate,-6))

vRefDateRangeMax:

=MonthEnd(AddMonths(vRefDate,12))

vRefDateRange:

='>=$(vRefDateRangeMin)<=$(vRefDateRangeMax)'

Sum( {<aDate={"$(vRefDateRange)"}>} Sales )

B) Another way of making it more maintainable and testable using only a single variable could be:

vRefDate:

=AddMonths(If(GetSelectedCount(aDate)=0, Today(1) , Date(Min(aDate)) ) , $1 )

Sum( {<aDate={">=MonthStart($(vRefDate(-6)))<=MonthEnd($(vRefDate(12)))"}>} Sales )

C) You could of course hard-code the entire expression in a single set expression without using variables - although I think this is much harder to debug, maintain and reuse:

Sum( {<aDate={">=$(=MonthStart(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),-6)))<=$(=MonthEnd(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),12)))"}>} Sales )

5 Replies
md_qlikview
Contributor II

Re: Default Date selection in Set Analysis

Try using below expression

sum({<CalendarDate={">=($(=AddMonths(Date(Max(CalendarDate)),-6)))

=<($(=AddMonths(Date(Max(OrderDate)),+12)))"}>}SalesAmount)

vanderson009
Contributor

Re: Default Date selection in Set Analysis

Thanks Milind for your valuable reply.

But my requirements is not like this, in my calendar I have max date 31 Dec 2020 and as per your logic if I take

max(CalendarDate) and -6 then it will give me Jul-20 but I want current date -6 that is Jan-18 and that should be dynamic like if user click on Aug-18 then it should -6 from Aug-18 i.e. Feb-18.

Thanks.

MVP
MVP

Re: Default Date selection in Set Analysis

You have three possible selection scenarios:

1) The user hasn't selected anything and then the current date - today - should be used as reference date.

2) The user has selected a single date and then that date should be the reference date.

3) The user has selected multiple dates and then either the first or the last date should be the reference date.

A) This can be done by creating four variables and using the last variable vRefDateRange in the set expression:

vRefDate:

=Date(If( GetSelectedCount(aDate)=0 , Today(1) , Min(aDate) ) )

vRefDateRangeMin:

=MonthStart(AddMonths(vRefDate,-6))

vRefDateRangeMax:

=MonthEnd(AddMonths(vRefDate,12))

vRefDateRange:

='>=$(vRefDateRangeMin)<=$(vRefDateRangeMax)'

Sum( {<aDate={"$(vRefDateRange)"}>} Sales )

B) Another way of making it more maintainable and testable using only a single variable could be:

vRefDate:

=AddMonths(If(GetSelectedCount(aDate)=0, Today(1) , Date(Min(aDate)) ) , $1 )

Sum( {<aDate={">=MonthStart($(vRefDate(-6)))<=MonthEnd($(vRefDate(12)))"}>} Sales )

C) You could of course hard-code the entire expression in a single set expression without using variables - although I think this is much harder to debug, maintain and reuse:

Sum( {<aDate={">=$(=MonthStart(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),-6)))<=$(=MonthEnd(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),12)))"}>} Sales )

vanderson009
Contributor

Re: Default Date selection in Set Analysis

Thanks a lot petter-s

MVP
MVP

Re: Default Date selection in Set Analysis

You're welcome - glad to be of help