topic Re: YTD calculation in QlikView

YTD calculation

Fri, 08 Jul 2016 00:07:17 GMT

Hello,

Is there a way to calculate YTD, QTD, MTD etc. without having a proper date column?

Assume that all are scattered across multiple columns like, day, month and year in separate fields and we are not supposed to concatenate them.

Thanks

Re: YTD calculation

Mon, 11 Jul 2016 05:49:49 GMT

Hello,

Can someone please suggest some ideas to implement this?

Re: YTD calculation

qliksus — Tue, 12 Jul 2016 07:40:08 GMT

Can you share some more details on the list of tables which has the Dates,Year,Month column and how these table are connected it would be better you attach some file . The general approach is that you can have a master calendar with all required dates connected to the required tables and then have the MTD,YTD created based on that

Re: YTD calculation

Tue, 12 Jul 2016 16:07:00 GMT

Thanks for your reply. I would not be able to create a Master Calendar as my requirement has a different for all the calculations. The Fiscal calendar which i use has different number of days for each month, first day of month begins on different dates when compared to the regular calendar, number of days in a month could exceed 31 days on some months etc.

Please suggest me on how to approach this.

Re: YTD calculation

Clever_Anjos — Tue, 12 Jul 2016 16:35:41 GMT

"Assume that all are scattered across multiple columns like, day, month and year in separate fields and we are not supposed to concatenate them."

Silly question, why don´t create a date field that would make things easier?

Re: YTD calculation

Anil_Babu_Samineni — Tue, 12 Jul 2016 16:46:57 GMT

How about Value List (Synthetic Dimension)

Try to avoid the Value list and then try with Wildmatch with the validation. I don;t which expression you want to implement for measure.

- ANIL

Re: YTD calculation

Tue, 12 Jul 2016 16:48:15 GMT

Some of the months have more than 32 which wouldn't be considered as a date if we concatenate them.

Re: YTD calculation

Tue, 12 Jul 2016 16:49:37 GMT

Sorry, i couldn't understand your suggestion. Can you please elaborate?

I need these date fields in all my calculations, so am not sure if i'l be able to achieve it using ValueList.

Re: YTD calculation

Tue, 12 Jul 2016 17:00:15 GMT

Attaching the Fiscal Calendar which should be used for all the calculations.

Columns like FISCALYEAR, FISCALQUARTER, FISCALMONTHNBR, FISCALDAYOFQTR, FISCALDAYOFMONTH are to be used for calculating YTD, QTD etc.