Month to date: Last year

[qlikviewaf]

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

[RonaldDoes]

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)

[qlikviewaf]

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

[RonaldDoes]

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)  

[qlikviewaf]

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

[Kushal_Chawda]

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)

[qlikviewaf]

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