topic Re: YTD Calculation from MTD in QlikView

YTD Calculation from MTD

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

Hi,

We have an report which captures the data from day 1 of the month by accumulating the data to month end (MTD data capture) .

Now we have planned to have an YTD report.

If we directly give the date range means it's not correct because its MTD.

So to get YTD numbers we have planned to capture only month end value.

Logic: sum(Dollars)

Year start: Jan-2016

Please let me know how to get YTD by including only month end dollar value of every month.

Thanks..

Re: YTD Calculation from MTD

MK_QSL — Mon, 28 Nov 2016 19:53:46 GMT

What are the dimensions you have in your calendar ?

I mean Date, Month, Year, YearMonth ?

Where you want this output? Chart/Table, Text box?

Re: YTD Calculation from MTD

Anonymous — Tue, 29 Nov 2016 04:29:33 GMT

Hi,

Can you share your date format(MTD or other)? so we can undrestant that what function we can apply for calculating YTD.

Thanks,

Re: YTD Calculation from MTD

nareshthavidishetty — Tue, 29 Nov 2016 12:26:46 GMT

Hi Manish,

We are having date and table required is pivot.

Hi Pooja,

'MM/DD/YYYY' format.

Thanks..

Re: YTD Calculation from MTD

MK_QSL — Tue, 29 Nov 2016 12:40:55 GMT

Please read my query and provide more information.

Re: YTD Calculation from MTD

Anonymous — Tue, 29 Nov 2016 13:06:09 GMT

hi,

as of your requirement....

you can use....

dimension as MonthEnd(your date field) and

expression as sum(Dollars)

by this you get...month end wise sum of dollars in every year....based on your data

then you have to filter only this year(2016) month ends.

Re: YTD Calculation from MTD

MK9885 — Tue, 29 Nov 2016 13:31:08 GMT

MonthEnd(YourDateField) as MonthEnd <<< write this in Master Calendar

And in front end, use MonthEnd as Dim

Sum(Dollar) as expression.

Re: YTD Calculation from MTD

Anonymous — Tue, 29 Nov 2016 13:50:03 GMT

Yes,

You can also calculate in script level

MonthEnd(Date field) as MonthEnd

then create a..report

Re: YTD Calculation from MTD

nareshthavidishetty — Tue, 29 Nov 2016 17:31:44 GMT

Hi,

I need logic in front end.

My requirement is like

sum(sales) where reported date = 02/28/2016,03/31/2016,04/30/2016,05/31/2016....till year end month.

Thanks..

Re: YTD Calculation from MTD

Anonymous — Wed, 30 Nov 2016 03:37:08 GMT

Hi Naresh,

Try this......

Dimension As if(Match(Year(MonthEnd(Date Field)),'2016'),MonthEnd(Date Field))

and

Expression as Sum(Dollars)