Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
jessica_webb
Contributor III

Evaluate variable for each dimension value

I have a table with dimension 'Point' (which has four values: One, Two, Three, Four) and expression:

=Num(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current),IsAcad)),'#,##.#')  &' (' & $(vAcadAPS_Grade_Current) & ')'

The variables are:

vAPS_Current = SUM(Current_points)/SUM(Qual_size)

vAcadAPS_Grade_Current =

IF(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current),IsAcad))>=60,'A*',

IF(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current),IsAcad))>=56.67,'A*-',

IF(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current),IsAcad))>=53.34,'A+',

IF(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current),IsAcad))>=50,'A',

IF(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current),IsAcad))>=46.67,'A-',)))))))))))))


All works as I would expect, but it doesn't give the expression value for each dimension value.


So, with all selections cleared I get the following table:

OneTwoThreeFour
EXP:47.9 (A-)()()()

Then, if I select 'Point' Two, I get the following table:

OneTwoThreeFour
EXP:()55 (A+)()()

I don't understand why it isn't evaluating for each dimension value?

Does anyone know what I can do to have all cells filled with the correct value?

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Evaluate variable for each dimension value

Hi Jessica,

Please try to add nodistinct into your aggr function, like aggr(nodistinct .....)

Hope it can help you.

Thanks.

Aiolos

View solution in original post

19 Replies
Highlighted

Re: Evaluate variable for each dimension value

If you use this without the variable (directly in your chart), does it still do the same thing? Or is this only when you use it as a variable?

Highlighted
jessica_webb
Contributor III

Re: Evaluate variable for each dimension value

If I remove all the part in red:

Num(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current),IsAcad)),'#,##.#')  &' (' & $(vAcadAPS_Grade_Current) & ')'


Then it evaluates across all dimension values (just incorrectly).


So I guess it's actually an issue with the expression, rather than the variable...

Highlighted

Re: Evaluate variable for each dimension value

Is ONE, TWO, THREE, FOUR (up top) a dimension also? What is it's name? IsAcad or something else?

Highlighted
jessica_webb
Contributor III

Re: Evaluate variable for each dimension value

No, One, Two, Three & Four are the values for the dimension 'Point'

Highlighted

Re: Evaluate variable for each dimension value

I assume, One , Two, Three .. are the dimension which translate from column to Row. Could be Dual() needed here for measure

=Dual(Num(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current),IsAcad)),'#,##.#')  &' (' & $(vAcadAPS_Grade_Current) & ')', Aggr($(vAPS_Current), IsAcad))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

Re: Evaluate variable for each dimension value

Try this

Num(ONLY({<IsAcad={'-1'}>} Aggr($(vAPS_Current), IsAcad, Point)),'#,##.#')  &' (' & $(vAcadAPS_Grade_Current) & ')'

Highlighted
jessica_webb
Contributor III

Re: Evaluate variable for each dimension value

No joy there unfortunately!

That gives me:

OneTwoThreeFour
EXP()()()
Highlighted
jessica_webb
Contributor III

Re: Evaluate variable for each dimension value

Thanks for the suggestion Anil. Unfortunately that's not working.

I just get:

OneTwoThreeFour
EXP()()()
Highlighted

Re: Evaluate variable for each dimension value

What do you get for this alone? Anything?

Num(Only({<IsAcad={'-1'}>} Aggr($(vAPS_Current), IsAcad, Point)), '#,##.#')