Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vanderson009
Creator III
Creator III

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
petter
Partner - Champion III
Partner - Champion III

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 )

View solution in original post

5 Replies
md_qlikview
Creator II
Creator II

Try using below expression

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

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

vanderson009
Creator III
Creator III
Author

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.

petter
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

Thanks a lot petter-s

petter
Partner - Champion III
Partner - Champion III

You're welcome - glad to be of help