topic Re: Month to date: Last year in QlikView

Month to date: Last year

qlikviewaf — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

i have a report where the user needs to select a specific date: e.g. 13/06/2017

Then i got a table that shows the Month-to-date build with the follwoing expression:

Sum({<Year=, Month=, Quarter=, Week=, Day=,Invoice_Date=, Invoice_Date_Num={">=$(=Num(MonthStart(Max(Invoice_Date_Num))))<=$(=Max(Invoice_Date_Num))"}>}

ExtendedUSD)

Which is working.

How i can create the related expression to have the MTD of the previous year?

Thank you so much

Re: Month to date: Last year

RonaldDoes — Fri, 16 Jun 2017 12:55:11 GMT

Hi Antonio,

You can try:

Sum({<Year=, Month=, Quarter=, Week=, Day=,Invoice_Date=,  Invoice_Date_Num={">=$(=Num(MonthStart(AddYears(Max(Invoice_Date_Num), -1)))<=$(=AddYears(Max(Invoice_Date_Num), -1))"}>}
ExtendedUSD)

Re: Month to date: Last year

qlikviewaf — Fri, 16 Jun 2017 13:10:35 GMT

nothig appears with this expression on my graph what can i do?

Re: Month to date: Last year

RonaldDoes — Fri, 16 Jun 2017 13:17:48 GMT

Hmm, It's hard without an example QVW. Maybe:

=Sum({<Year=, Month=, Quarter=, Week=, Day=,Invoice_Date=, Invoice_Date_Num={'>=$(=Num(MonthStart(AddYears(Max(Invoice_Date_Num),-1)))<=$(=AddYears(Max(Invoice_Date_Num),-1)))'}>}  
ExtendedUSD)

Re: Month to date: Last year

qlikviewaf — Fri, 16 Jun 2017 13:45:05 GMT

One thing, the "Invoice Date Num" is a numeric field like 42901 for 15/06/2017 - could be this the issue?

Re: Month to date: Last year

Kushal_Chawda — Fri, 16 Jun 2017 14:01:44 GMT

try below

Sum({<Year=, Month=, Quarter=, Week=, Day=,Invoice_Date=, Invoice_Date_Num={">=$(=Num(MonthStart(addyears(Max(Invoice_Date_Num),-1))))<=$(=addyears(Max(Invoice_Date_Num),-1))"}>}

ExtendedUSD)

Re: Month to date: Last year

qlikviewaf — Fri, 16 Jun 2017 14:31:07 GMT

a filter is missing here since i got as MTD millions...so seems that is taking int the account several months