YTD Calculation

[nareshthavidishetty]

Hi ,

I need to compare this year YTD with Lastyear YTD sales.

Date= SoldDate

Ex:

Thanks..

[sunny_talwar · Accepted solution]

Try this:

=Sum({<SoldDate = {

"$(='>=' & Date(YearStart(Max(SoldDate), 0), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(YearStart(Max(SoldDate), -1), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(YearStart(Max(SoldDate), -2), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(YearStart(Max(SoldDate), -3), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)

[nareshthavidishetty]

Hi Sunny,

It's working fine.

Is the below logic works for MTD as same as YTD.

=Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(Max(SoldDate), 0), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(Max(SoldDate), -1), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStartMonthStart(Max(SoldDate), -2), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(Max(SoldDate), -3), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)

Thanks..

[sunny_talwar]

You want to still show 2014, 2015, 2016, & 2017?

[nareshthavidishetty]

Yes.

I need to show year as well.

Thanks..

[sunny_talwar]

May be this

=Sum({<SoldDate = {

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(MonthStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)

[nareshthavidishetty]

Hi,

Got results but it is showing only for month dec.

I have checked the data is available.

Thanks..

[sunny_talwar]

Month is a dimension? I am not sure what you doing, can you share an updated sample?

[nareshthavidishetty]

Thanks..

Is the below expression is correct for WTD.

=Sum({<SoldDate = {

"$(='>=' & Date(WeekStart(AddYears(Max(SoldDate), 0)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), 0), 'M/D/YYYY'))",

"$(='>=' & Date(WeekStart(AddYears(Max(SoldDate), -1)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -1), 'M/D/YYYY'))",

"$(='>=' & Date(WeekStart(AddYears(Max(SoldDate), -2)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -2), 'M/D/YYYY'))",

"$(='>=' & Date(WeekStart(AddYears(Max(SoldDate), -3)), 'M/D/YYYY') & '<=' & Date(AddYears(Max(SoldDate), -3), 'M/D/YYYY'))"

}, SoldMonth, SoldYear>}ESTCUST)

Thanks..

[nareshthavidishetty]

https://community.qlik.com/message/1232931#1232931

[sunny_talwar]

That is disheartening to see that after so much effort from my end, you marked your own response as correct. You did not find a single expression provided by me to be correct, not even helpful?