Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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
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.
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
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: