article QlikView App: Dynamic Date Dimension in Member Articles

QlikView App: Dynamic Date Dimension

stevedark — Wed, 29 May 2013 21:27:49 GMT

This example shows a chart with a Dynamic Date Dimension, that shows a different date type based on the possible values of each date type.

Natively QlikView allows for Drill Groups of fields, which makes it simple to create a drill down from Years, through Months and down to Dates. This works well, but does not allow the user to view months across two years, or even some months at the end of one year and the beginning of the next.

By using a Calculated Dimension this example chart chooses which date type to show, Year, Month or Date based on the number of possible values for each of those date types. This allows for a much more dynamic and intuitive view of values over time.

Steve Dark

http://www.quickintelligence.co.uk/

Re: QlikView App: Dynamic Date Dimension

stevedark — Fri, 08 Aug 2014 21:36:36 GMT

Please note that I have posted an update to this application, which allows drilling down to hourly intervals - should you need to do so:

QlikView App: Dynamic Date Dimension - With Hours

Enjoy.

Steve

Re: QlikView App: Dynamic Date Dimension

jrdunson — Wed, 11 Feb 2015 15:27:29 GMT

Steve,

Thanks for this... I've tried to setup this in a dashboard...and it seems to work, except for the scale of the grid... for instance, if I drill down to less than 36 months...here is what I get..the grid stays the same, though the values are less. How do we re-size the scale also? - Jarrell

Re: QlikView App: Dynamic Date Dimension

jrdunson — Wed, 11 Feb 2015 15:28:37 GMT

I also tried under Layout: "Size to Data" but it didn't work

Re: QlikView App: Dynamic Date Dimension

jrdunson — Wed, 11 Feb 2015 15:42:30 GMT

Steve, found a solution...using "Show All Bars" (and tweeking the formulas), so you can disregard. Jarrell

Re: QlikView App: Dynamic Date Dimension

donleeqlikview — Thu, 23 Jul 2015 14:48:59 GMT

Hi Guys,

Can you give some example of this kind of App including the Quarter date.

Year > Quarter > Months > Days

thanks a lot

Re: QlikView App: Dynamic Date Dimension

stevedark — Thu, 23 Jul 2015 18:44:23 GMT

You can achieve this by adding another nested if, between year and month. You will need to do this for both dimension and legend.

If you don't have any joy let me know and I will take a look when I am next able.

Re: QlikView App: Dynamic Date Dimension

donleeqlikview — Mon, 03 Aug 2015 08:49:43 GMT

Thanks Steve, I already got it.

My problem now is how to enhance the charts of qlikview.

maybe you can give me some sample.

Thanks a lot

Don

Re: QlikView App: Dynamic Date Dimension

stevedark — Tue, 04 Aug 2015 06:55:55 GMT

In what way enhance? There are a number of properties you can change that

will improve the look of your charts. If you can describe what is wrong

with the ones you have I may be able to help you enhance them.

Steve

Re: QlikView App: Dynamic Date Dimension

Anonymous — Thu, 28 Sep 2017 11:42:02 GMT

Very very interesting, thanks.

I am trying to complete some charts and this example is very useful.

But do you know if it also applicable for QlikSense ? thanks

Re: QlikView App: Dynamic Date Dimension

stevedark — Thu, 28 Sep 2017 13:23:30 GMT

Yes, it should work the same. In Sense now it is less required as you have the continuous scale and the scrolling functionality. What this doesn't do though is aggregate up the values in the different date periods - rather it just squashes the line and changes the legend.

Re: QlikView App: Dynamic Date Dimension

Anonymous — Tue, 19 Dec 2017 11:28:18 GMT

Hi Steve,

Thank you for this. I've created another variable for 12 months previous:

if(GetPossibleCount([CalDate])<=36 , Date(addmonths([CalDate],-12) , 'DD/MM/YYYY'),

if(GetPossibleCount([CalMonthYear])<=36, Date(addmonths([CalMonthYear],-12),'MMM YYYY'),

Date(addyears([CalYear],-1),'YYYY')))

Which works to generate the correct time period but the expression is still calculating based on this years selected dates. So I'm getting the correct months/dates but the wrong count. Any ideas?

Here is my expression:

COUNT(DISTINCT{<[Provider Type]={'Consultant','Radiographer - Diagnostic','Radiographer - Diagnostic, Manager',

'Sonographer'}>}[Order ID])

Many thanks

Jess

Re: QlikView App: Dynamic Date Dimension

stevedark — Tue, 19 Dec 2017 11:37:29 GMT

Hi Jess,

You don't need the AddMonths or AddYears. What is happening is although you are modifying the display of the dimensions they are still relating back to the original values. If you clicked on a month that you had done a -12 on you will note it will select the month in this year not last.

The dimension needs to remain as it was, and if you pick a prior year it will zero in on the months in that year.

What is the actual requirement you have?

It may be that some of the code in my prior period app will be of help?

QlikView App: Set Analysis - Prior Period Comparison

Steve

Re: QlikView App: Dynamic Date Dimension

Anonymous — Tue, 19 Dec 2017 17:13:15 GMT

This methodology is helpful and something I haven't used yet. Typically I create a group to cycle thru Year, Quarter, Month & Week. I look forward to adding this new logic to app soon.

Re: QlikView App: Dynamic Date Dimension

stevedark — Tue, 19 Dec 2017 17:21:35 GMT

Thank you! Glad you like it.