YTD comparision with previous year as leap year

[remyasujith]

Hi,

I have YTD and MTD comparison report, since 2021 feb has 28 days 2020 feb is taking 28 days only but i have to show sales for 29 days. Please help to solve this.

[Or]

What exactly is the problem? The number of days in a month should be immaterial to YTD/MTD comparison.

[remyasujith]

Hi

Thanks for your reply.

I am showing MTD and Last year MTD in a report, if user select a month like feb it will show this year feb sales in MTD and last year feb sales in LYMTD. In this case since feb 2021 has 28 days LYMTD shows 28 days sales but I want to show 29 days sales. 

Below is the formula using for last year

Sum({<Date = {"$(='>=' & Date(AddYears(Min(Date), -1), 'DD/MM/YYYY') & '<=' & Date(AddYears(Max(Date), -1), 'DD/MM/YYYY'))"}>}Sales)

[Or]

For YTD, you can use the built-in YearToDate() function. https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

or the built-in InYearToDate() function. https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

For MTD, you can use the built-in InMonthToDate() function. https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

All of these should prevent you from having to work around leap years, I believe, as they handle the logic for you.

[PrashantSangle]

try below

Sum({<Date = {"$(='>=' & Date(AddYears(Min(Date), -1), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date), -12), 'DD/MM/YYYY'))"}>}Sales)

Regards,

Prashant Sangle

[remyasujith]

Hi, I tried but not working. am using the below formulas

For Last Year MTD

(Sum({<Year=,Month=,Date=,DateNum={">=$(=Num(MonthStart(AddYears(Max(DateNum),-1))))<=$(=Num(AddYears(Max(DateNum),-1)))"}>}Sales)

For Last Year YTD

(Sum({<Year={'$(vPriorYear)'},Month=,DayNumber=,Date=,[Year - Month]=, DateNum={"<=$(=num(date(addyears(max(Date), -1))))"}>}Sales)

[PrashantSangle]

For Last Year MTD

(Sum({<Year=,Month=,Date=,DateNum={">=$(=Num(MonthStart(AddYears(Max(DateNum),-1))))<=$(=Num(MonthEnd(Max(DateNum),-13)))"}>}Sales)

Regards,

Prashant Sangle

[remyasujith]

The below expression is working 

Sum({<Year=,Month=,Date={">=$(=Date(MonthStart(Min(Date), -12), 'DD/MM/YYYY'))<=$(=Date(MonthEnd(Max(Date),-12), 'DD/MM/YYYY'))"} >}

but the issue is with current month MTD will show till march 8 but LY MTD is showing till march 31st. 

TIA.

[PrashantSangle]

in this case use if else statement

like if(max(Date)<>MonthEnd(max(Date)),

Sum({<Year=,Month=,Date=,Date={">=$(=Num(MonthStart(AddYears(Max(Date),-1))))<=$(=Num(AddYears(Max(Date),-1)))"}>}Sales

,

Sum({<Year=,Month=,Date={">=$(=Date(MonthStart(Min(Date), -12), 'DD/MM/YYYY'))<=$(=Date(MonthEnd(Max(Date),-12), 'DD/MM/YYYY'))"} >}

)

Regards,

Prashant Sangle