Calculated Dimension for YTD

Anonymous — Wed, 15 Nov 2017 15:43:49 GMT

Good afternoon,

For the life of me I cannot figure this out, and due to my expressions being calculated fields for my list of KPI's, I am stuck using dimensions. I'm having an issue, firstly, finding information to creating YTD as a calculated dimension (no problem for expressions), and secondly having issues trying to do it in a numeric way.

My setup for times:

DATE_NUMERIC YYYY-MM-DD

DATE_TK integer of YYYYMM

MONTH_NAME MMMM

MONTH_NAME_ABBREVIATION MMM

MONTH_NUMBER_OF_QUARTER #

MONTH_NUMBER_OF_YEAR # (1-12)

MONTH_NUMERIC ###### -- resembles: YYYYMM

QUARTER_NAME 1st Quarter, etc

YEAR_NUMERIC YYYY

These are the values from my DWH, I am not responsible for their namings nor can I change them outside of what I do in Qlik.

I first tried to find a solution for YTD, based on dates, but have given up on that route; unless someone here has a better idea? Also, my YTD must function dynamically since I have a listbox where the month's are selectable. E.g. MAR_YTD would be Jan+Feb+Mar, even though values exists into october or november of that given year.

Originally, I thought of doing a calculated dimension using MONTH_NUMERIC, and subtracting 12, since 12 will never get to a previous year, e.g. 201109 - 12 = 201097, thereby giving all of the results from JAN to SEP, but avoiding anything else in 2010. Any help on this matter would be greatly appreciated.

The QVD was put together specifically with spoofed data to aid in assistance.

Cheers!

-Mikael

Re: Calculated Dimension for YTD

YoussefBelloum — Wed, 15 Nov 2017 15:59:51 GMT

Hi,

Read this https://community.qlikview.com/thread/11506

and try the johnw‌ example !

Re: Calculated Dimension for YTD

Anonymous — Wed, 15 Nov 2017 18:32:01 GMT

This doesn't suite my needs, as its a solution through the scripting and changing the data model. I'd prefer to do this through just the calculated dimension only. If I must, I could join an inline table that has flags, in a 12 field, 12 row table, that flags each month_ytd with respective months, but this doesn't seem as if its the best solution; there must be a better way to do it through a calc dimension, no?

Cheers.

topic Re: Calculated Dimension for YTD in QlikView

Calculated Dimension for YTD

Re: Calculated Dimension for YTD

Re: Calculated Dimension for YTD