Limit a dimension

[nils_jakob]

Hi

 I'm developing my first app in Qlik Sense. I only work with expressions in the visualization part, not in the script.

I do have difficulties in limiting the dimensions the way I want in charts. 

For example I have a line chart where the dimension i year and the measure is actual and budget. The years go from 1970 to 2019. In the line chart I only want to show 2017-2019 (without the users need to select this manually. How can I solve this?

Another example is a bar chart showing the budget deviation per line in the financial statements. The bar chart is now showing all the lines including the aggregated sums. I want to show only the costs, and none of the aggregated sums. How do I do this?

Thanks in advance and happy new year,

Nils Jaklob 

[sunny_talwar · Accepted solution]

You can either use a calculated dimension or use set analysis. Set analysis will be more appropriate, but there is a use case of calculated dimension as well.

Set Analysis will be used within your expression. Assuming the expression is Sum(Measure), you will change it to this

Sum({<YearField = {">=2017<=2019"}>}Measure)

or if this is based Max Year, then this

Sum({<YearField = {">=$(=Max(YearField) - 2)<=$(=Max(YearField))"}>} Measure)

Calculated dimension

If(YearField >= 2017 and YearField <= 2019, YearField)

or if this is based on max year

If(YearField >= (Max(TOTAL YearField) - 2) and YearField <= Max(TOTAL YearField), YearField)

[nils_jakob]

Thanks. It works 🙂

[OmarBenSalem]

If this works, then please like Sunny's post and ACCEPT his answer as a solution; that way:

1) You thank him for his contribution (we're not payed to respond, we also have jobs... a thank you is, and always will be welcome)

2) the thread is closed.

Thanks 🙂

[KishoreSuri]

Hi Sunny,

We have  a requirement that Dimension be a variable and wants the same dimension to control with 'N' Value. We want to use this variable dimension in Trellis Container.

For ex: We have a list of dimensions(Customer, country,state,region) and have taken this dimension as a variable $(vDim) with Sum(Amt) expression. This variable taken in a filter and Bar chart in Trellis container wants to change as per filter selection.

We want to control Top N values (Values = Customer/ country/state/region) in dimension.

We used the expression "=If($(vDim) > 0 and $(vDim) <= 10, $(vDim))" ,but getting error as "expression incorrect ')' expected".

Please help us.

Thanks,

Kishore S

[Arnon_Yaffe]

Thank you very much,

What would be the syntax of the expression in order to limit the Top N largest items in the dimension?

Thanks