QlikView App: Dynamic Date Dimension

[stevedark]

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/

[Anonymous]

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

[stevedark]

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

[Anonymous]

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. 

[stevedark]

Thank you!  Glad you like it.