Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chrisellis
Contributor

Dimension - Using Calculated Variables (Do they execute?)

Could someone explain in a chart how to use a calculated variable in a dimension to produce a column in as table.

in the load script  the variable vAttendance is defined and works correctly;

Let vAttendance = '((Sum([Present])+Sum([Activity])+Sum([Study Leave]))/(Sum([Present])+Sum([Authorised])+Sum([UnAuthorised])+Sum([Activity])))';

The above produces a column that contains values in the format of 0.00'

If I then create a table add the vAttendance into the table as a measure then try to reference it in the dimension to create another column using

if((vAttendance)<0.90,'90 Below','Above 90')

This only returns one value for all figures even though some are below 0.90.

I think the variable is not executing in the dimension.

Many Thanks

Chris

6 Replies

Re: Dimension - Using Calculated Variables (Do they execute?)

You might need to use Aggr() function on which you are summing your vAttendance

Aggr(If(vAttendance < 0.90, '90 Below', 'Above 90'), DimensionName/s)

rupamjyotidas
Valued Contributor

Re: Dimension - Using Calculated Variables (Do they execute?)

This equation won't work :if((vAttendance)<0.90,'90 Below','Above 90')

As you should understand, after you have defined it, vAttendance will have value now which is (for example)

100

vAttendance=100

Then you dimension becomes

if(100<0.90,'90 Below','Above 90'), and it would always show 'Above 90'.

chrisellis
Contributor

Re: Dimension - Using Calculated Variables (Do they execute?)

Hi Sunny,

Tried it like this and accepts the syntax but still only show the 90 Below on all.

Aggr(If((vAttendance) < 0.90, '90 Below', 'Above 90'), Pupil)

Below is a screen shot of what is happening.

Thanks

Chris

chrisellis
Contributor

Re: Dimension - Using Calculated Variables (Do they execute?)

Thanks,

If I wanted to group results from a calculation would it be better to do this in the load script or the chart ?

Thanks

Chris

lakshmikandh
Valued Contributor II

Re: Dimension - Using Calculated Variables (Do they execute?)

yes without grouping your variable always aggregates at all level,

you have to use aggr function Aggr() function

Re: Dimension - Using Calculated Variables (Do they execute?)

Even if you scroll all the way to the bottom it doesn't show Above 90? Can you may be share your qvf for us to take a look at?