Re: YTD calculation - Qlik Community

Report Inappropriate Content · ‎2016-07-07

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

Report Inappropriate Content · ‎2016-07-11

Hello,

Can someone please suggest some ideas to implement this?

qliksus · ‎2016-07-12

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

Report Inappropriate Content · ‎2016-07-12

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.

Clever_Anjos · ‎2016-07-12

"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?

Anil_Babu_Samineni · ‎2016-07-12

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Report Inappropriate Content · ‎2016-07-12

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

Report Inappropriate Content · ‎2016-07-12

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.

Report Inappropriate Content · ‎2016-07-12

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.