Chart Calculation Condition for only some field values

[quiquehm]

Hi !

I am getting mad trying to enter a display condition for a chart ( in this case for a pivot table ). I simply want to display a reduced set of field values from one of the Dimensions I have in the pivot table. I tried to use Set Analysis ( a Set Modifier ) but it seems this only works when you use any aggregation function with the field , like for example Sum ( {<Year={2008}>}Sales).

In my case I simply need to display the Expression in my table ( in this case Dates ) for a reduced set of field values from one of the Fields used as Dimension ( in this case MilestoneTypeDate ). This setting should not be affected by the selections made on the MilestoneTypeDate field.

Any suggestions ?

Thanks a lot

[#2]

You can add a calculated dimension......and detached object by right click....

[quiquehm]

Thanks Érico. I know detaching an object will work as it freezes the data for the selections made. The problem is when I make a new selection ( on a different dimension to the one I need to remain frozen ) the object obviously does not pick up the new selections ( as it was detached ). I believe I don´t understand what you propose about adding a calculated dimension. I would need my pivot table to remain live when I select a different project number for example. My first idea was to try to setup a calculation condition for the table, something like this below :

Chart Properties-->General-->Calculation Condition : {<MilestoneType={DesignDate,BuildDate,ShipDate}>}Dates

(Dates field is the only Expression I have in the pivot table )..all the other fields are dimensions ( like ProjectNr, MilestoneNr, MilestoneType..etc )

But I learnt that set modifiers only work with aggregations...and I don´t need any aggregation for the Dates field.

Please let me know if I explained myself correctly. I appreciate any help or hints you can provide ( this is driving me crazy ).

Thanks a lot

Enrique

[johnw]

So you have an expression that's just the field "Dates"? And you only want the row to display if Milestonetype is 'DesignDate' or 'BuildDate' or 'ShipDate'?

If so, the highest performance is probably to use this expression instead:

only({<MilestoneType={'DesignDate','BuildDate','ShipDate'}>} Dates)

You say you don't need aggregation for your expression, and that may be correct. But the only() aggregation will give the same result, while allowing you to use set analysis. Think of it as a cheat, if you wish. I personally think of it as more correct to use only(). A language like SQL would FORCE you to use aggregations, since the chart dimensions are the equivalent of an SQL GROUP BY. QlikView is more forgiving, but coming from an SQL background, I think it's a good habit to at least THINK of the dimensions as GROUP BYs, and the expressions as aggregations, even if you shorthand it and just put "Dates" in as your expression because you know there will be only one and QlikView lets you get away with that. How you think of it doesn't really matter, though, as long as you recognize that only(Dates) will return the same result as Dates while allowing you to add set analysis, and is thus a useful tool for the toolbox.

Another option is a calculated dimension like Érico mentioned (not a calculation condition, a calculated dimension). Instead of dimension MilestoneType, add a calculated dimension, and use this:

if(match(MilestoneType,'DesignDate','BuildDate','ShipDate'),MilestoneType)

The result in the chart should be the same. I suspect the set analysis approach is higher performance. However, if you had ten expressions in your chart, for instance, the calculated dimension would be much easier than putting the set analysis expression in every single chart expression, and might go faster. Only testing would tell you for sure. But since you only have one dimension, I'd probably go with set analysis.

[quiquehm]

John, I CAN´T THANK YOU ENOUGH FOR THAT DETAILED EXPLANATION !!

I tried the only() aggregation and it works perfect !!!. I am still a begginner on QlikView and I learn new stuff everyday. Thanks again .

Also thanks to Érico for the calculated dimension suggestion. I will keep it in my list of lessons learned as well.

Rgds

Now I can go to bed for a good sleep ( it´s midnight here in Madrid ) :-)))