# Qlik Sense App Development

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

## 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

## 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

## 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

## 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

## 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