Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
One minor thing, have you been closing the parenthesis at the end of these expressions?
Hey Sunny, closing parenthesis got lost during copying
works, thanks a lot!!
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
#Sunny_talwar
Parabéns ótima resposta. Consegui resolver um problema que tenho a mais de duas semanas.