Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I'm new to Qlikview, i'm trying to sum the value of transactions for yesterday date the previous year.
This the set analysis i use to get yesterday's figure and it works perfectly.
<SPAN style="FONT-SIZE: 10pt" mcestyle="font-size: 10pt;"><SPAN style="FONT-SIZE: 8pt" mcestyle="font-size: 8pt;">
sum({<Revenue.Date={">=$(=date(today()-1),'DD/MM/YY'))"}>} Revenue.Net)
However if i apply the logic that the same day last year would be 366 days earlier it doesn't work.
<SPAN style="FONT-SIZE: 10pt" mcestyle="font-size: 10pt;"><SPAN style="FONT-SIZE: 8pt" mcestyle="font-size: 8pt;">
Does anyone have any bright ideas on how i can achieve it?
many thanks
Kate - I had similar requirement few months back. I mean I had to calculate Date, Month, Year for transaction data. Then I had to calculate the SameDatePrevious, SameMonthPreviousYear, PreviousMonth, PreviousYear.
I had used variables to achieve this task. Something like this...
CurrentYear = Max({1} Year)
PreviouYear = Max({1} Year) - 1
CurrentMonthNumber = Max({1<Year = {$(=$(vMaxYear))}>} MonthNumber)
PreviousMonthNumber = IF($(vCurrentMonthNumber) = 1, 12, $(vCurrentMonthNumber) - 1)
The idea is to seperate the Time dimension in to different elements like Date, Month, Year and then we use these variables within Set Analysis.
Good luck!
Cheers - DV
Hi Kate,
Have you tried using the AddMonths(Date, -12) function? You can pass thee value -12 as the second paramenter to get the Last year date... Is that what you need?
Regards,
Cesar