topic Re: YTD Formula in QlikView

YTD Formula

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

I have a line chart where ill be having month as dimension

I need to show 24 months from aug back to 24 months

Measure is YTD

any idea?

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 09:46:50 GMT

May be this? and you can use which field you want degrade.

Sum({<DateField = {">=$(=AddYears(Max(Month),-2)) <=$(=Max(Month))"}>}Sales)

Re: YTD Formula

arvind_patil — Thu, 17 Aug 2017 10:01:36 GMT

Hi Gowtham,

May be like this:

Sum({<Date= {">=$(=Addmonths(Max(Month),-24)) <=$(=Max(Month))"}>}Value)

Thanks,

Arvind Patil

Re: YTD Formula

gautik92 — Thu, 17 Aug 2017 12:08:10 GMT

this will select the date but wat abt ytd in this formula

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 12:25:33 GMT

You can simply define the YearStart() in your first operator

Sum({<DateField = {">=$(=YearStart(AddYears(Max(Month),-2))) <=$(=Max(Month))"}>}Sales)

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 12:26:10 GMT

Sum({<DateField = {">=$(=YearStart(AddYears(Max(DateField),-2))) <=$(=Max(DateField))"}>}Sales)

Re: YTD Formula

gautik92 — Thu, 17 Aug 2017 12:34:34 GMT

If I use this it will take year start I don't want like that ur first expression looks good it fetches date from aug 15 to july 17 but I need to sum the data lik for aug 15 I need to sum data from april 15-august15

same like for all the months

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 12:41:20 GMT

If second expression doesn't help then why do you call name it as YTD? Do you know the definition of YTD? And you says, you need last 24 months? But here for Aug 15 it is indicating to me 5 months data only when select on month as Aug 15. I am not sure, I followed you this moment

Re: YTD Formula

gautik92 — Thu, 17 Aug 2017 13:54:32 GMT

Hi anil thanks for your reply

I have attached a data please check

in that ill be having sum in the second column and third column is having ytd

if you see the third column till apr 15 the value will be fine but after apr 15 its summing up with the old data

but my output should be like if its apr 15 it shud show sum(Leaver)and may shud sum up apr 15 and may 15 and so on .Please check the file I have attached.

my third column formula is

=IF(Period1=YearStart(Period1,0,4),Sum(VOLUNTARY_LEAVER),RangeSum(Above(Sum(VOLUNTARY_LEAVER),0,12)))

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 13:58:42 GMT

If you are using straight table use Full Accumulation radio button to achieve.

If it is in pivot table then use like below

Rangesum(

Below(

Sum({<DateField = {">=$(=YearStart(AddYears(Max(Month),-2))) <=$(=Max(Month))"}>}Sales)

,0),

Above(Sales,1,RowNo(TOTAL)))

Re: YTD Formula

gautik92 — Thu, 17 Aug 2017 14:01:19 GMT

I want to show it in line chart

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 14:02:29 GMT

Use the same formula.

Re: YTD Formula

gautik92 — Thu, 17 Aug 2017 14:07:34 GMT

its not working in straight table itslef

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 14:08:28 GMT

please share QVW

Re: YTD Formula

gautik92 — Thu, 17 Aug 2017 14:21:54 GMT

Please check

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 14:43:36 GMT

I think you need this not the YTD? Please change title to How to achieve this? Because, you need to refine the data from and fiscal year. May be look the links?

Fiscal Year‌

Fiscal and Standard Calendar generation

Re: YTD Formula

Anil_Babu_Samineni — Thu, 17 Aug 2017 14:50:52 GMT

And, Can you confirm till how many months you need accumulate? I am expecting bucket like (From 5th month - Which month)

Re: YTD Formula

gautik92 — Fri, 18 Aug 2017 06:09:33 GMT

If month is august then april to august if it is march then from last year april to march same like that

Re: YTD Formula

Anil_Babu_Samineni — Fri, 18 Aug 2017 06:50:09 GMT

Please read first document and implement same. You will deserve this