topic Re: Chart with one axis for multiple dates in QlikView

Chart with one axis for multiple dates

Mon, 26 Mar 2012 11:50:03 GMT

Hi @ all,

I've a problem creating a chart with the following requirements:

I want to show 2 formulas in a chart where on depends on the creation date and one on the closing date.I have a classical star schema with a fact table and surrounding dimensions, in special a dim_creation_time and dim_closing_time for handling the different dates.

Now my idea was to have a third date_dimension called dim_date_range which is not connected to the fact table. This dimension should be the dimension in a line chart. But How do I connect this separated dimension to the other ones in the formulars?

Thanks for your help.

Chart with one axis for multiple dates

fosuzuki — Tue, 27 Mar 2012 23:03:18 GMT

You could use expressions like:

sum(if(dim_date_range = dim_creation_time, YourFactMeasure))

and

sum(if(dim_date_range = dim_cclosing_time, YourFactMeasure))

beware that this may not work well with a large data volume because of the if().

Hope this points you in the right direction.

Regards.

Chart with one axis for multiple dates

Wed, 28 Mar 2012 07:39:21 GMT

Hi.

thanks for your answer. I already tried this way.

sum(if(open_date_field=date_range_field,count_fact_value)) works. but

sum({$<open_date_field=date_range_field>}count_fact_value) doesn't work and gives me "no data available".

But I have the calucation for the measure already in a set analysis formula and I think it would be really hard to transform this into a "normal" if-clause.

best regards

Arne

Chart with one axis for multiple dates

fosuzuki — Fri, 30 Mar 2012 12:49:07 GMT

Hi Arne,

Sorry, but isn't it possible for you to mix the if-clause with your current set analysis? It'd be something like this:

sum({set analysis} if(open_date_field=date_range_field,count_fact_value) )

Regards,

Fernando

Re: Chart with one axis for multiple dates

johnw — Fri, 30 Mar 2012 23:52:04 GMT

As Fernando says, an if() with an island date will perform poorly. Consider linking the new date to your data model. See attached, though the example probably has a lot of features you don't need - it's linking multiple dates in multiple tables, and where two of the tables share an ID but one has multiple rows per ID. The basic idea should remain the same, though - a linkage table with a date, a date type, and your key fields. In more complicated data models than the one shown, the linkage table could cause loops, but probably won't for simpler cases.

Re: Chart with one axis for multiple dates

Mon, 29 Oct 2012 00:07:51 GMT

Hi,

If possible could you load an example that is compatible with the personal edition? Or alternatively post the script code you are demonstrating.

Thanks

Damian

Re: Chart with one axis for multiple dates

johnw — Mon, 29 Oct 2012 16:01:39 GMT

It isn't possible for me to make an example compatible with Personal Edition. Only QlikTech can do that. But you can find the script, object definitions, and a bit of explanation here:

http://community.qlik.com/message/269159#269159