topic Re: Chart time dimension based on min and max dates in QlikView

Chart time dimension based on min and max dates

linoyel — Wed, 29 Jul 2015 14:22:59 GMT

Hi all,

On one of Qlikview tasks I was asked to build a chart where time dimension was supposed to be dynamically calculated and the dates range was supposed to be between min and max dates.

I wasn't sure how to do it and I was told there is an option in Qlikview to set min and max values in charts...

Does anyone familiar with this option?

I'll appreciate your answers,

Thanks

Re: Chart time dimension based on min and max dates

MK_QSL — Wed, 29 Jul 2015 14:26:17 GMT

Use Set Analysis with Min and Max Date criteria.

SUM({<Date=,Year=, Week=, Month=, Quarter=, Date = {">=$(=Min(Date))<=$(=Max(Date))"}>}Sales)

Re: Chart time dimension based on min and max dates

linoyel — Wed, 29 Jul 2015 14:28:32 GMT

OK.. this is the expression, but how I build a dimension based on the interval between min(date) and max(date)?

Re: Chart time dimension based on min and max dates

MK_QSL — Wed, 29 Jul 2015 14:30:18 GMT

Provide sample application. Would try for you!

Re: Chart time dimension based on min and max dates

dickelsa — Wed, 29 Jul 2015 14:30:55 GMT

it should work automatically if you check 'surpress when value is nul' for your dimension?

Re: Chart time dimension based on min and max dates

antoniotiman — Wed, 29 Jul 2015 14:45:37 GMT

Hi Linoy,

If Dim is Your Dimension, Date Your Date

vMinDate Variable Min Date, vMaxDate Variable Max Date

In Chart (e.g. Pivot Table)

Dimension :

=Aggr(If(Date >= '$(vMinDate)' and Date <= '$(vMaxDate)',Dim),Dim)

Regards,

Antonio

Re: Chart time dimension based on min and max dates

linoyel — Wed, 29 Jul 2015 15:12:09 GMT

Hi Antonio, this looks like a reasonable solution, I'll try that

Thanks

Re: Chart time dimension based on min and max dates

linoyel — Wed, 29 Jul 2015 16:38:47 GMT

OK, attached

The task is to show the chart only if one of the projects (Project Name) is chosen by user.

Every project has versions and there is a field that indicates if it is the last version or not.

Let's say the chart has to show how many "bad" Stats and how many "good" Stats there were for a specific project.

The dimension is supposed to be between min and max dates which are supposed to be calculated in a following way:

we need to take only last version's Project Start Date, for min value we need to take 2 months before that date and for max value we need to take 3 months after that date.

For example, if we choose 'bbb' project, the last version's date is 15/03/2015. So min date for this project would be 15/01/2015 and max date would be 15/06/2015.

Re: Chart time dimension based on min and max dates

antoniotiman — Wed, 29 Jul 2015 20:05:08 GMT

See attachment

Re: Chart time dimension based on min and max dates

linoyel — Thu, 30 Jul 2015 07:36:28 GMT

Yes!

Thanks, Antonio