topic Re: Limiting Dimension Field based on Expression value in QlikView

Limiting Dimension Field based on Expression value

ZoeM — Sat, 16 Nov 2024 05:03:04 GMT

Hi Community.

I have a combo bar/line chart with one dimension (year...2012 - current) and two expressions.

The first expression has values for all the years available (bars on chart).

The second expression has values for only some of the years (more recent years) and is represented by the line on chart.

In the chart, I would only like to show the years where there are values for the second expression.

I thought I would have been able to use Conditional on the dimension tab in properties with an expression that says:

Sum(Sales)>0,

but it didnt work. I may have had the syntax wrong or maybe I may be looking in the wrong tab in the properties.

any ideas provided will be tried and tested.

Thanks in advance.

Re: Limiting Dimension Field based on Expression value

sunny_talwar — Wed, 05 Dec 2018 16:16:55 GMT

Add an Aggr() function to do this

Aggr(If(Sum(Sales) > 0, Year, Year)

Re: Limiting Dimension Field based on Expression value

ZoeM — Wed, 05 Dec 2018 16:24:35 GMT

I have set up my conditional expression as follows:

=Aggr(If( Sum({$<[Date Type]={'VPP Dates'}>}[Function NTEI])>0),Year)

but I did not get the desired result...

Re: Limiting Dimension Field based on Expression value

sunny_talwar — Wed, 05 Dec 2018 16:28:26 GMT

You mean your conditional dimension, right? Try this

=Aggr(If(Sum({$<[Date Type] = {'VPP Dates'}>} [Function NTEI])>0, Year), Year)

Re: Limiting Dimension Field based on Expression value

ZoeM — Wed, 05 Dec 2018 16:31:32 GMT

I have applied it in the "Enable Conditional", but it is not working.

Are you thinking maybe a calculated dimension?

Re: Limiting Dimension Field based on Expression value

sunny_talwar — Wed, 05 Dec 2018 16:34:13 GMT

Enable condition is to show or hide a particular dimension... why do you want to use it there? I think you might want to try it as a calculated dimension....

Re: Limiting Dimension Field based on Expression value

ZoeM — Wed, 05 Dec 2018 16:54:41 GMT

Very true. As a calculated dimension it works as expected so i will go with that. I was under the impression I would have been able to accomplish the same task without having to create a calculated dimension, but I am glad that I was able to find a simple solution!

Thanks and I appreciate how quickly you did it!

Re: Limiting Dimension Field based on Expression value

sunny_talwar — Wed, 05 Dec 2018 16:56:18 GMT

There is def. a solution which doesn't use calculated dimension... all you need to do is to add this set analysis to all your expressions

{<Year = {"=Sum({$<[Date Type] = {'VPP Dates'}>} [Function NTEI])>0"}>}

Re: Limiting Dimension Field based on Expression value

ZoeM — Wed, 05 Dec 2018 17:49:28 GMT

Awesome. The first way you advised is working like a charm.