Chart with one axis for multiple dates

[#1]

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.

[fosuzuki]

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.

[#3]

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

[fosuzuki]

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

[johnw]

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.

[#6]

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

[johnw]

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