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: 
Not applicable

Calculated dimension for year to date

Hello all,

We have a feature in my organisation's applications that shows week, month and year to date, based on our own financial year, in a single dimension. we do this by flagging specific dates as belonging to these categories and when the application loads, all the date records in the week to date are loaded in one table field and 'Week to Date' is loaded in a second field called 'Weekly Report'. We then load all the dates related to month and 'month to date' into the 'Weekly Report' field etc. Here is an example of how that table looks:

error loading image

I would now like to be able to create a dimension like this on the fly, based on a value that the user enters, to see what these values would have been at a point in time. I have experimented with set analysis and can generate the correct output, but as separate results fields rather than in one dimension, and I have tried a calculated dimension but found that the week results were omitted from the month which was omitted from the year.

Has anyone successfully achieved this kind of calculated dimension? I was wondering if a macro could be used to add values to a table, similar to the way the original dimension was built.

Many thanks,

NW

3 Replies
johnw
Champion III
Champion III

OK, let's see if I'm following. You want a field called "Weekly Report" with values 'Week to Date', 'Month to Date', 'Quarter to Date' and 'Year to Date'. You want to be able to put this field in a chart as the dimension. Then if you do something like sum(Amount) in the chart, you want a week to date sum for the 'Week to Date' value, and so on. You have this already, but it's only based on today's date. You want to be able to SELECT a date for it to be based on, so if you select April 9, 2010 for instance, your Week to Date should be based on April 5 - April 9, 2010.

If so, I've never done it in a real application, but I've written a lot of examples. Here's the basic idea - you want to create an AsOf table that connects an "As of Date" to a REAL "Date" via your "Weekly Report" field:

As of Date, Weekly Report, Date
20100818, Week to Date, 20100818
20100818, Week to Date, 20100817
20100818, Week to Date, 20100816
20100818, Month to Date, 20100818
20100818, Month to Date, 20100817
...
20100818, Month to Date, 20100801
20100818, Quarter to Date, 20100818
20100818, Quarter to Date, 20100817
...
20100818, Quarter to Date, 20100701
20100818, Year to Date, 20100818
20100818, Year to Date, 20100817
...
20100818, Year to Date, 20100101
20100817, Week to Date, 20100817
...

Create a list box for "As of Date" and force one and only one value to be selected. Select the current date by default. Default behavior should then be what you're seeing today, but if they change the selection, they'll see the date as of the selected date.

The closest script I have to what I think you want is actually the most recent thing I've written, which is some discarded script in a live application I'm building. I'm only using week ending dates in this case, and since I was just testing, I used an AUTOGENERATE 35 rather than calculating the number of weeks in the data set. But hopefully you can figure out how to modify that part. You may also need to modify the definition of a week. Below, it starts on Monday and the first week of the year always starts on January 1.

AsOf:
LOAD date(date#(20100102,'YYYYMMDD')+(recno()-1)*7) as "As of Date"
AUTOGENERATE 35
;
LEFT JOIN ([AsOf])
LOAD fieldvalue('Date',iterno()) as Date
AUTOGENERATE 1
WHILE len(fieldvalue('Date',iterno()))
;
INNER JOIN ([AsOf])
LOAD *
RESIDENT [AsOf]
WHERE Date <= "As of Date"
AND year(Date) = year("As of Date")
;
LEFT JOIN ([AsOf])
LOAD * INLINE [
"Date Range"
WTD
MTD
YTD
];
INNER JOIN ([AsOf])
LOAD *
RESIDENT [AsOf]
WHERE "Date Range" = 'YTD'
OR ("Date Range" = 'MTD' and monthstart(Date)=monthstart("As of Date"))
OR ("Date Range" = 'WTD' and weekstart(Date)=weekstart("As of Date"))
;

Not applicable
Author

Thanks for the great example there, John. Your expertise with the more advanced inter-record iterative functions is much greater than mine and I couldnt get your example to work. Even just copying the code and pasting it into an empty application gives me an empty table when I run the script.

I did borrow from your logic though and create the AsOf dimension using a for.. next loop and working back through the week structure, loading the relevant dates that matched the various criteria for that week. I think your solution looks more elegant though, so I'm going to have another go at getting to grips with it.

Many thanks,

Noel

johnw
Champion III
Champion III

Looping works too, and should be more memory-efficient than the approach I took, which pretty much makes all possible combinations and then starts removing ones it doesn't like. The difference could matter if you have a whole lot of dates and limited RAM. The most efficient for both memory AND CPU would probably be to handle the loop with a WHILE statement. Just tried this out in my application instead of my original approach, and it seems OK on the surface:

Dates:
LOAD fieldvalue('Date',iterno()) as Date
AUTOGENERATE 1
WHILE len(fieldvalue('Date',iterno()))
;
AsOf:
LOAD
"Date" as "As of Date"
,'WTD' as "Date Range"
,date("Date" - iterno() + 1) as "Date"
RESIDENT Dates
WHILE week("Date") = week("Date" - iterno() + 1)
AND year("Date") = year("Date" - iterno() + 1)
;
CONCATENATE (AsOf)
LOAD
"Date" as "As of Date"
,'MTD' as "Date Range"
,date("Date" - iterno() + 1) as "Date"
RESIDENT Dates
WHILE month("Date") = month("Date" - iterno() + 1)
;
CONCATENATE (AsOf)
LOAD
"Date" as "As of Date"
,'YTD' as "Date Range"
,date("Date" - iterno() + 1) as "Date"
RESIDENT Dates
WHILE year("Date") = year("Date" - iterno() + 1)
;
DROP TABLE [Dates]
;