YTD - Last year to a given date of the last year

[MadeleineT1]

Hi, 

I am trying to calculate the YTD sales : Last year sales at a given date. 

In particular,  the same date of today last year in order to make a comparative analysis of sales.

I made a variable with 'makedate' but now i'm not able to use it in the set analysis.

Can someone please throw any suggestions?

thanks

[vinieme12]

Current Year YTD =

sum({<Date={">=$(=Date(yearstart(Max(Date)),'YYYY-MM-DD'))<=$(=Date(Max(Date),'YYYY-MM-DD'))"}>}Sales)

Previous Year YTD =

sum({<Date={">=$(=Date(yearstart(Max(Date),-1)'YYYY-MM-DD'))<=$(=Date(addyears(Max(Date),-1),'YYYY-MM-DD'))"}>}Sales)

change YYYY-MM-DD to be same as  the date format of your Date field

[MadeleineT1]

Hi, 

Thank you, but it doesn't work, it seems it is summing all without filtering the period selected.

this is the YTD that i used for the current year, and it works:

=Sum({<Year=, Month=, Quarter=, Week=, DataRegistraz(DataField)=, DateNum(Floor(DataField)={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

For the Previous Year;

sum({<(DataField)={">=$(=(DataField)(yearstart(Max((DataField)),-1)'YYYY-MM-DD'))<=$(=(DataField)(addyears(Max((DataField)),-1),'YYYY-MM-DD'))"}>}Sales)

where did i go wrong?

thx

Madeleine

[MadeleineT1]

i found that if i select years 2021 and 2022 it sums current year and last year  in the last YTD  while in the current YTD just the 2022 sales

[vinieme12]

=Sum({<Year=, Month=, Quarter=, Week=, DataRegistraz(DataField)=, DateNum(Floor(DataField)={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

For the Previous Year;

sum({<(DataField)={">=$(=(DataField)(yearstart(Max((DataField)),-1)'YYYY-MM-DD'))<=$(=(DataField)(addyears(Max((DataField)),-1),'YYYY-MM-DD'))"}>}Sales)

1)DataRegistraz(DataField)   Is this a fieldname or are you trying to calculate something?

2)DateNum(Floor(DataField)  you cannot have calculations on the left side of the = in set analysis, this must be an actual field

3)YearStart(Max(DateNum)))    Do not use DateNum ; Date functions such as year(), week(),month() will not work on a numeric representation of Date, you have to use the actual Date field not a numeric value of the date

4)$(=(DataField)(yearstart(Max((DataField)),-1)'YYYY-MM-DD'))   ;  What i've used us the date() to format the date; this does not refer to the date field name

Hope the below is clear enough, just replace "YourDateFieldName" with your date field name in your data model

date() used below is simply to format the date value

Current Year YTD =

sum({<YourDateFieldName={">=$(=Date(yearstart(Max(YourDateFieldName)),'YYYY-MM-DD'))<=$(=Date(Max(YourDateFieldName),'YYYY-MM-DD'))"}>}Sales)

Previous Year YTD =

sum({<YourDateFieldName={">=$(=Date(yearstart(Max(YourDateFieldName),-1)'YYYY-MM-DD'))<=$(=Date(addyears(Max(YourDateFieldName),-1),'YYYY-MM-DD'))"}>}Sales)

[MadeleineT1]

1) DataRegistraz(DataField) : i mean that DataRegistraz is the DataField

2) DateNum(Floor(DataField) : in the script i calculated DateNum as Floor(DataRegistraz)

Sorry,  i'm new to Qlikview and what i wrote wasnt't clear!

I'll try again! Thank you!

[vinieme12]

Modified your expressions to use the date field "DataRegistraz"

CurrentYear

=Sum({<DataRegistraz={">=$(=Date(YearStart(Max(DataRegistraz))))<=$(=Date(Max(DateNum)))"}>} Sales)

PreviousYear

=Sum({<DataRegistraz={">=$(=Date(YearStart(Max(DataRegistraz),-1)))<=$(=Date(addyears(Max(DateNum),-1)))"}>} Sales)

Please post a screenshot of the Edit expression box like below, so we can see what the expression evaluates to

and also confirm selections made when taking the screenshot