Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Using date ranges in set analysis

Hi all,

I'm struggeling using a specific date range for a KPI object:

I want to calculate the sum of sales of the last 30 days. Date is stored in a dimension called [Booking Date] and the sales amount is stored in [Amount in Euro].

Now, let's assume my newest Booking Date is the 11th of November 2014, then this function gives me exactly what I am looking for:

Sum({$<[Booking Date] = {">=2014-10-12<=Max([Booking Date])"}>} [Amount in Euro])

Now, my issue is that I am not able to use any function for the lower bound of the function above. I tried things like

  1. Sum({$<[Booking Date] = {">=Min([Booking Date])<=Max([Booking Date])"}>} [Amount in Euro]
  2. Sum({$<[Booking Date] = {">=2014-10-12 + -30<=Max([Booking Date])"}>} [Amount in Euro]
  3. Sum({$<[Booking Date] = {">=Max([Booking Date]) - 30<=Max([Booking Date])"}>} [Amount in Euro]
  4. Sum({$<[Booking Date] = {">=Max([Booking Date]) <=Max([Booking Date])"}>} [Amount in Euro]


All just sum up to 0. Particularly interesting is example 4. Since the boundaries are "larger or equal" and "smaller or equal" I would expect to see the sum of all entries with that particular booking date.

Any help would be highly appreciated!

Thank you


Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Using date ranges in set analysis

2nd expression can be done like this:

Sum({$<[Booking Date] = {">=$(=Date(AddMonths(2014-10-12, -1), 'YYYY-MM-DD')) <= $(=Date(Max([Booking Date]), 'YYYY-MM-DD'))"}>} [Amount in Euro])

3rd expression

Sum({$<[Booking Date] = {">=$(=Date(AddMonths(Max([Booking Date]), -1), 'YYYY-MM-DD')) <=$(=Date(Max([Booking Date]), 'YYYY-MM-DD'))"}>} [Amount in Euro])

HTH

Best,

Sunny

6 Replies
MVP
MVP

Re: Using date ranges in set analysis

Try this may be:


Sum({$<[Booking Date] = {">=$(=Date(Min([Booking Date]), 'YYYY-MM-DD'))<=$(=Date(Max([Booking Date]), 'YYYY-MM-DD'))"}>} [Amount in Euro])



Best,

Sunny

MVP
MVP

Re: Using date ranges in set analysis

2nd expression can be done like this:

Sum({$<[Booking Date] = {">=$(=Date(AddMonths(2014-10-12, -1), 'YYYY-MM-DD')) <= $(=Date(Max([Booking Date]), 'YYYY-MM-DD'))"}>} [Amount in Euro])

3rd expression

Sum({$<[Booking Date] = {">=$(=Date(AddMonths(Max([Booking Date]), -1), 'YYYY-MM-DD')) <=$(=Date(Max([Booking Date]), 'YYYY-MM-DD'))"}>} [Amount in Euro])

HTH

Best,

Sunny

MVP
MVP

Re: Using date ranges in set analysis

One minor thing, have you been closing the parenthesis at the end of these expressions?

  1. Sum({$<[Booking Date] = {">=Min([Booking Date])<=Max([Booking Date])"}>} [Amount in Euro])
  2. Sum({$<[Booking Date] = {">=2014-10-12 + -30<=Max([Booking Date])"}>} [Amount in Euro])
  3. Sum({$<[Booking Date] = {">=Max([Booking Date]) - 30<=Max([Booking Date])"}>} [Amount in Euro])
  4. Sum({$<[Booking Date] = {">=Max([Booking Date]) <=Max([Booking Date])"}>} [Amount in Euro])
Not applicable

Re: Using date ranges in set analysis

Hey Sunny, closing parenthesis got lost during copying

Not applicable

Re: Using date ranges in set analysis

works, thanks a lot!!

MVP
MVP

Re: Using date ranges in set analysis

Awesome that it worked. I didn't even realize that this was a QlikSense discussion. I have never used QlikSense but I guess the set analysis is probably the same

Best,

Sunny