topic Re: YTD Calculation in QlikView

YTD Calculation

Wed, 25 Nov 2020 16:16:04 GMT

H all,

i want to calculate a YTD of year that i select.

For example if i select 2016-02 i want a sum of value of 2016-01 and 2016-02 etc...

Like field I have year_month (eg. YYYY-MM)

Can somebody help me?

Re: YTD Calculation

ziadm — Mon, 11 Apr 2016 16:22:24 GMT

Hi Cecilia

use this set analysis

set vMaxYear = Max(Year)

Set vMaxDate =date(max(Date), 'DD MMM YYYY')

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month=>}Qta)

Thanks

Re: YTD Calculation

sunny_talwar — Mon, 11 Apr 2016 16:28:54 GMT

Did you get time to look at my response here -> Re: Prior year for year_month field

Re: YTD Calculation

ziadm — Mon, 11 Apr 2016 16:46:56 GMT

of Follow the following thread

QlikView App: Simple Year To Date Example (Set Analysis)

Re: YTD Calculation

satishkurra — Mon, 11 Apr 2016 16:48:03 GMT

Check the attached

Re: YTD Calculation

Tue, 12 Apr 2016 08:25:17 GMT

Hi Satish, if we use a text object the solution works, but i would see the value 350 in the following table, below the date 02/01/2016, but it doesn't appear .

What can i do?

Thanks

Re: YTD Calculation

satishkurra — Tue, 12 Apr 2016 22:09:46 GMT

I understand u have done this using pivot table. Now go to presentation tab and select Date dimension and check show partial sum option. Now you can see total

Let me know

Re: YTD Calculation

Wed, 13 Apr 2016 07:41:21 GMT

I Satish, i explain you better what I would. I attach a qvw with the pivot table in which I want below 02/01/2016 the value 350, below 03/01/2016 the value 700 and so on…. Like a progressive values…

I hope I have explain my issue better.

Re: YTD Calculation

satishkurra — Wed, 13 Apr 2016 19:41:45 GMT

I don't think we can achieve this...

Re: YTD Calculation

sunny_talwar — Wed, 13 Apr 2016 19:43:45 GMT

Why not Satish? Try this:

=RangeSum(Before(Sum({$<Date={">=$(=YearStart(Max(Date)))<=$(=Max(Date))"}>}[Sales]) , 0, ColumnNo()))

Re: YTD Calculation

agni_gold — Wed, 13 Apr 2016 20:02:26 GMT

Somethingh like that ?

Re: YTD Calculation

satishkurra — Wed, 13 Apr 2016 21:50:59 GMT

Apology guys. I should have thought about this before replying...

Re: YTD Calculation

sunny_talwar — Wed, 13 Apr 2016 21:54:07 GMT

No apology needed my friend... Thats how we all learn, isn't it?

Re: YTD Calculation

satishkurra — Wed, 13 Apr 2016 22:44:10 GMT

Hmm yes

Re: YTD Calculation

Fri, 15 Apr 2016 07:52:15 GMT

Hi Sunny, with the field Year_Month doesn’t work, could be this because I don’t have a date YYYY-MM-DD or I’m wronging the formula?

Help me please ☹

Re: YTD Calculation

agni_gold — Fri, 15 Apr 2016 16:36:59 GMT

elaborate please

Re: YTD Calculation

sunny_talwar — Fri, 15 Apr 2016 16:40:47 GMT

What exactly are you trying to get?

Re: YTD Calculation

Mon, 18 Apr 2016 07:05:59 GMT

I want YTD of Prior and Current year, but with the formula =RangeSum(Before(Sum({$}Qta)), 0, ColumnNo()))

I don’t’ think it is wrong…..

Re: YTD Calculation

sunny_talwar — Mon, 18 Apr 2016 09:22:42 GMT

This?

Current

=RangeSum(Before(Sum({$<ReportYearMonth={">=$(=YearStart(Max(ReportYearMonth)))<=$(=Max(ReportYearMonth))"}, Flag = {'CY'}>}Qta), 0, ColumnNo()))

=RangeSum(Before(Sum({$<ReportYearMonth={">=$(=YearStart(Max(ReportYearMonth)))<=$(=Max(ReportYearMonth))"}, Flag = {'PY'}>}Qta), 0, ColumnNo()))

Re: YTD Calculation

Mon, 18 Apr 2016 12:22:12 GMT

Hi Sunny,

now works!!!!!!! ☺ thanks thanks thank !