Limiting Dimension Field based on Expression value

[ZoeM]

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. 

[sunny_talwar]

Add an Aggr() function to do this

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

[ZoeM]

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...

[sunny_talwar · Accepted solution]

You mean your conditional dimension, right? Try this

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

[ZoeM]

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

Are you thinking maybe a calculated dimension?

[sunny_talwar]

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....

[ZoeM]

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!

[sunny_talwar]

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"}>}

[ZoeM]

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