Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
Thanks. It works 🙂
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 🙂
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
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