topic Re: trouble limiting canonical week Calculated Dimension in QlikView

trouble limiting canonical week Calculated Dimension

Wed, 18 Mar 2015 14:39:11 GMT

I have a chart where the dimension is a set of canonical WeekName()'s (called CanonicalWk)

I only want to show weeks after the minimum date in another non-canonical week field, say ShippedWk. In other words, I'm lopping off all the empty weeks on the left side of the chart up to the earliest week with Shipping data, then showing all weeks after that. In the test data, this minimum week happens to be 2014/23.

Another note - I have to display all canonical weeks after this date whether they have 0 values or not, so I can't simply use the Suppress options on the Presentation tab.

I assume that the right way to do this is with a Calculated Dimension.

If I hard code the minimum date into the Calculated Dimension like this, I get exactly what I want:

=if(CanonicalWk>='2014/23',CanonicalWk)

I also know that this yields the string 2014/23.

=MinString({$<status={'X','Y'}>} ShippedWk)

But if I combine those into this Calculated Dimension, I get "Error in calculated dimension"

=if(CanonicalWk>=MinString({$<status={'X','Y'}>} ShippedWk),CanonicalWk)

Is this a syntax issue, or am I missing something bigger?

Re: trouble limiting canonical week Calculated Dimension

Gysbert_Wassenaar — Thu, 19 Mar 2015 13:03:37 GMT

Try =if(CanonicalWk>=$(=MinString({$<status={'X','Y'}>} ShippedWk)),CanonicalWk)

Re: trouble limiting canonical week Calculated Dimension

Thu, 19 Mar 2015 13:28:48 GMT

No difference - same error.

I should mention too that there are no errors (red underlining) shown as I populate the Calculated Dimension field. The chart shows a single bar, and the dimension label beneath the bar is "// Error in calculated dimension"

Re: trouble limiting canonical week Calculated Dimension

Mon, 23 Mar 2015 16:50:03 GMT

Ah. Syntax. Unless it's an integer, single quotes are needed. The correct answer is:

=if(CanonicalWk>='$(=MinString({$<status={'X','Y'}>} ShippedWk))',CanonicalWk)