topic Re: YTD calculation in QlikView

YTD calculation

Fri, 20 Nov 2020 18:02:30 GMT

Hi,

I have only year and month fields. and on selection of year and month, it should give me the addition of consecutive months (january onwards, upto selected month/year)

For ex, on selecting 2014 March, i want sum of January2014 + february 2014 + march 2014 value.

How can it be done

Thanks.

Re: YTD calculation

senpradip007 — Tue, 07 Oct 2014 08:48:01 GMT

Try like:

Sum({<Year={$(=Max(Year))}, Month={'<=$(=Max(Month))'}>} YourField)

Re: YTD calculation

Tue, 07 Oct 2014 08:52:05 GMT

thanks, i have written the same expression. but it gives me only selected month/year value. and doesnt take the previous years in calculation.

Re: YTD calculation

PrashantSangle — Tue, 07 Oct 2014 08:55:09 GMT

Hi,

Try this,

Sum({<Year={"$(=Max(Year))"}, Month={"<=$(=Max(Month))"}>} YourField)

If possible can share your sample apps

Regards

Re: YTD calculation

tresB — Tue, 07 Oct 2014 08:58:06 GMT

Hi,

This sort of analysis majorly depends on data type(how you calculate them in the script...); specially date formats. Hence, request you to create a sample qvw and share.

Re: YTD calculation

ashfaq_haseeb — Tue, 07 Oct 2014 09:08:41 GMT

Hi,

Check this

YTQ, QTD, MTD and WTD

Regards

ASHFAQ

Re: YTD calculation

Tue, 07 Oct 2014 09:28:42 GMT

how do i post the qvw. i dont find any option to post it.

Re: YTD calculation

senpradip007 — Tue, 07 Oct 2014 09:31:45 GMT

Click on Reply link, then click Use advanced editor and attach your sample qvw.

Re: YTD calculation

Tue, 07 Oct 2014 09:34:34 GMT

i am using google chrome and find no option of advanced editor

Re: YTD calculation

senpradip007 — Tue, 07 Oct 2014 09:38:52 GMT

I'm also using chrome as well. First click Reply link, then you will find Use advanced editor link.

Re: YTD calculation

ashfaq_haseeb — Tue, 07 Oct 2014 09:44:43 GMT

Hi,

If you reply via inbox you don't see that option.

try to open it in a new window.

Then click on reply, then you will see advance editor on bottom right.

Regards

ASHFAQ

Re: YTD calculation

Tue, 07 Oct 2014 09:45:27 GMT

Re: Re: YTD calculation

Tue, 07 Oct 2014 09:52:29 GMT

Thanks

Please check the attached qvw

Re: Re: YTD calculation

ashfaq_haseeb — Tue, 07 Oct 2014 09:58:20 GMT

Check this

Regards

ASHFAQ

Re: YTD calculation

tresB — Tue, 07 Oct 2014 10:09:24 GMT

Try:

=Sum({<Year={$(=only(Year))},Month_Name, Month={"<=$(=Max(Month))"}>} [DS1Volume])

Like this, Month_Name field selection is disregarded to get your YTD.

Re: Re: Re: YTD calculation

PrashantSangle — Tue, 07 Oct 2014 10:10:45 GMT

Hi,

Please find attached example.

Regards

Re: Re: YTD calculation

Tue, 07 Oct 2014 10:15:51 GMT

but this way i get values for month Jan and Feb, and not the sum of Jan + Feb.

How do i find the sum of jan+ feb in ytd1 column for Selection Feb 2014

Re: YTD calculation

ashfaq_haseeb — Tue, 07 Oct 2014 10:16:10 GMT

Try like this

Sum({<Year={"=$(=max(Year))"},Month_Name=,Month={"<=$(=Max(Month))"}>}[DS1Volume])

Regards

ASHFAQ

Re: YTD calculation

tresB — Tue, 07 Oct 2014 10:21:13 GMT

Yes, if you have Month in the dimension, you have play a trick, like:

=RangeSum(Above(Sum({<Year={$(=only(Year))},Month_Name, Month={"<=$(=Max(Month))"}>} [DS1Volume]),0, RowNo(Total)))

Re: YTD calculation

Tue, 07 Oct 2014 10:21:32 GMT

No, it doesnt provide me the sum.