Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.