Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculated Dimension for YTD

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

2 Replies
YoussefBelloum
Champion
Champion

Hi,

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

and try the johnw‌ example !

Anonymous
Not applicable
Author

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.