topic YTD Calculation in QlikView

YTD Calculation

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

Hi ,

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

Date= SoldDate

Ex:

Thanks..

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 16 Mar 2017 07:55:49 GMT

Create Straight table and the use these expressions and use dim as Product

2017(YTD)

Sum({<Year={'Only({<Year = {'$(=Max(Year))'}>}Year)'}, Month=, Quarter=, Week=, SoldDate={">=$(=Num(YearStart(Max(SoldDate))))<=$(=Max(SoldDate))"}>} Sales)

2016(YTD)

Sum({<Year={'Only({<Year = {'$(=Max(Year)-1)'}>}Year)'}, Month=, Quarter=, Week=, SoldDate={">=$(=Num(YearStart(Max(SoldDate))))<=$(=Max(SoldDate))"}>} Sales)

2015(YTD)

Sum({<Year={'Only({<Year = {'$(=Max(Year)-2)'}>}Year)'}, Month=, Quarter=, Week=, SoldDate={">=$(=Num(YearStart(Max(SoldDate))))<=$(=Max(SoldDate))"}>} Sales)

2014(YTD)

Sum({<Year={'Only({<Year = {'$(=Max(Year)-3)'}>}Year)'}, Month=, Quarter=, Week=, SoldDate={">=$(=Num(YearStart(Max(SoldDate))))<=$(=Max(SoldDate))"}>} Sales)

Re: YTD Calculation

sunny_talwar — Thu, 16 Mar 2017 08:56:29 GMT

Re: YTD Calculation

nareshthavidishetty — Thu, 16 Mar 2017 09:30:00 GMT

Thanks..

Can we get in single expression.

Ex:

2017 - 233

2016- 334

Thanks..

Re: YTD Calculation

nareshthavidishetty — Thu, 16 Mar 2017 10:34:38 GMT

Hi,

I have to show as below format.ow to get this i single expression.

Thanks..

Re: YTD Calculation

nareshthavidishetty — Thu, 16 Mar 2017 11:57:05 GMT

Hi Sunny,

Can we get with in one expression.

Thanks..

Re: YTD Calculation

sunny_talwar — Thu, 16 Mar 2017 11:58:25 GMT

We might be able to, but I would need to look at what you have.... would you be able to share a sample?

Re: YTD Calculation

nareshthavidishetty — Thu, 16 Mar 2017 12:02:00 GMT

Hi,

After using the 4 expressions i got below result.Which is not correct.

I need to show in single column.

EX:For state Albuqerque.

Re: YTD Calculation

sunny_talwar — Thu, 16 Mar 2017 12:06:28 GMT

Can you attach you qvw?

Re: YTD Calculation

nareshthavidishetty — Thu, 16 Mar 2017 13:31:46 GMT

Hi,

Please find the attached is the application.

Thanks..

Re: YTD Calculation

sunny_talwar — Thu, 16 Mar 2017 13:35:32 GMT

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)

Re: YTD Calculation

nareshthavidishetty — Thu, 16 Mar 2017 13:58:58 GMT

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..

Re: YTD Calculation

sunny_talwar — Thu, 16 Mar 2017 14:03:04 GMT

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

Re: YTD Calculation

nareshthavidishetty — Thu, 16 Mar 2017 14:22:09 GMT

Yes.

I need to show year as well.

Thanks..

Re: YTD Calculation

sunny_talwar — Thu, 16 Mar 2017 14:28:53 GMT

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)

Re: YTD Calculation

nareshthavidishetty — Thu, 16 Mar 2017 14:46:23 GMT

Hi,

Got results but it is showing only for month dec.

I have checked the data is available.

Thanks..

Re: YTD Calculation

sunny_talwar — Thu, 16 Mar 2017 14:47:25 GMT

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

Re: YTD Calculation

nareshthavidishetty — Fri, 17 Mar 2017 04:13:32 GMT

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..

Re: YTD Calculation

nareshthavidishetty — Fri, 17 Mar 2017 06:32:33 GMT

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

Re: YTD Calculation

sunny_talwar — Fri, 17 Mar 2017 08:19:21 GMT

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?