Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Reliability-Engineer
Contributor III
Contributor III

Using Variable in expression for Graph

Currently I have the below graph based on a value in the table (TIJD)

I do however want to base it on a calculated value.  For this calculated value I created a Variable :  vTL%

vTL% : (((Sum({<VolgNr ={1}>}[Tijd]) + Sum({<VolgNr ={2}>}[Tijd])+Sum({<VolgNr ={3}>}[Tijd]) + Sum({<VolgNr ={4}>}[Tijd])+Sum({<VolgNr ={5}>}[Tijd]) + Sum({<VolgNr ={6}>}[Tijd])+Sum({<VolgNr ={7}>}[Tijd])
 +
Sum({<VolgNr ={8}>}[Tijd])+ Sum({<VolgNr ={9}>}[Tijd]) + Sum({<VolgNr ={10}>}[Tijd])+ Sum({<VolgNr ={11}>}[Tijd]) + Sum({<VolgNr ={12}>}[Tijd])) / Sum({<OE_HfdCode={'TijdBenut'}>}[OE_Tijd]))) * 100

Putting this in the default expresion does not work      Sum({<[Zone]={'Zone 9-10'}>} $(vTL%)

How must I setup my expression to make this work?

Thanks.

Jan

QV-01.jpg

Labels (3)
1 Solution

Accepted Solutions
Reliability-Engineer
Contributor III
Contributor III
Author

I'll follow your advice.

Thanks for helping so far.

Jan Droog

View solution in original post

10 Replies
marcus_sommer

Your approach doesn't work because you are nesting aggregation-functions without an aggr() and this isn't valid.

To get it working it should rather look like:

Sum({<[Zone]={'Zone 9-10'}>} aggr(
   Sum
({<VolgNr ={"<=12"}>}[Tijd]) / Sum({<OE_HfdCode={'TijdBenut'}>}[OE_Tijd]) * 100,
   $(=getcurrentfield('YourDimensionGroupName'))))

Here your redandant summing of the various VolgNr are shortened into a single calculation by adjusting the set analysis. The "<=12" might be extended with a >= or replaced with just a listing like 1,2,3, ... Further the needed dimension for the aggr is fetched from the used dimension-group. And the further set analysis is applied within the outer aggregation.

- Marcus

Reliability-Engineer
Contributor III
Contributor III
Author

I changed the expression as you proposed. Still no graph.

I first changed it to a single calculation. That worked well.

Then with the aggr(.............  Still no graph.

What's missing?

Sum({<[Zone]={'Zone 9-10'}>} aggr(
Sum({<VolgNr ={"<=12"}>}[Tijd]) / Sum({<OE_HfdCode={'TijdBenut'}>}[OE_Tijd]) * 100,
$(=GetCurrentField([Periode-QCDM]))))

The GroupName is equal to the one mentioned in 'Demensions'

QV-01.jpgsection

 

 

marcus_sommer

The dimension-group isn't treated as a classical field and needs therefore no [] brackets for itself else just type it without brackets or any quotes and ignore any syntax highlightning errors on it, but the whole construct must be wrapped with brackets if the belonging fields have some special chars or spaces. This means it should be look like:

[$(=GetCurrentField(Periode-QCDM))]

If it's don't work switch your group on the Year-Month field and replace the getcurrentfield() with it - to check if the main-logic worked.

- Marcus

Reliability-Engineer
Contributor III
Contributor III
Author

Marcus many thanks for your input so far. Unfortunately I didn't succeed yet.

I Think I tried what you proposed me to do.

Hope I give you enough information to give it another try Smiley Happy

QV-02.jpg

 

 

marcus_sommer

If you don't use the dimension-group within the chart you mustn't use getcurrentfield() else just the field like:

Sum({<[Zone]={'Zone 9-10'}>} aggr(
   Sum
({<VolgNr ={"<=12"}>}[Tijd]) / Sum({<OE_HfdCode={'TijdBenut'}>}[OE_Tijd]) * 100,
   Year))

- Marcus

Reliability-Engineer
Contributor III
Contributor III
Author

That did it Smiley Very Happy

Why however does the graph still show the same data in each graph despite the different Zone in the expression.

What I want is a worksheet with 5 graphs for each zone simultaneouslyQV-03.jpg

marcus_sommer

Are you sure that the values behind it are really different? Especially in regard to the association of the field Zone?

Another point could be the 1 as set modifier within you first chart.

- Marcus

Reliability-Engineer
Contributor III
Contributor III
Author

Yes the values are different.  It's now showing the avarage of all selection Zone's and it's value changes with a change of Zone.

Leaving the '1 ' doesn't change anything.  Using '$-1' and then selecting only Zone 1-2, results in an empty graph, as it should with this Set Modifier.  With '1' as Set Modifier it should only show the selected criteria (as mentioned in the HELP file).

marcus_sommer

Without knowing your data and the datamodel it's difficult to say what's wrong respectively different to the expectation.

But I think you will find the cause of your equal results with a bit playing with the expressions, for example by changing the Zone value to the another ones or just leaving it completely or maybe transfering it from the outer aggregation to the inner aggregation.
Further helpful is often not to use a graphically chart else a table chart and applying there several expressions in parallel and/or adding a few more dimensions. There should be occur some hint of what is happening.

- Marcus