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: 
jessica_webb
Creator III
Creator 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?

19 Replies
Anil_Babu_Samineni

Can we expect any file from you to investigate?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
uacg0009
Partner - Specialist
Partner - Specialist

Hi Jessica,

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

Hope it can help you.

Thanks.

Aiolos

jessica_webb
Creator III
Creator III
Author

Now that gives me more what I would expect...I get a value (albeit without the bracketed part), for each progress point!

This is great, thank you!

So now just need to work out what's wrong with the other part

jessica_webb
Creator III
Creator III
Author

Hi Anil,

I realise a file would probably be a big help! Unfortunately the one I'm working on is extremely confidential, so can't share that. I will try and put together some dummy data at some point today if I have time.

Thank you so much for your help,


Jess

jessica_webb
Creator III
Creator III
Author

Thanks Aiolos, that gets me values where I would expect them. But the wrong values!

Now I have:

OneTwoThreeFour
EXP51.45 (A)51.45 ()()()

So it''s not actually calculating per 'Point' - just averaging it out.

uacg0009
Partner - Specialist
Partner - Specialist

I think you also need to add "Point" into the aggr funcion as a group by field, could you please try?

Thanks

Aiolos

jessica_webb
Creator III
Creator III
Author

Definitely closer!! Thank you

OneTwoThreeFour
EXP47.9 (A-)55 ()()()

Just missing the value inside the brackets on 'Point' Two now...

uacg0009
Partner - Specialist
Partner - Specialist

Have you also add the nodistinct and Point into the variable's aggr?

Maybe don't need nodistinct, I don't have the data so I'm not sure, please have a try.

Thanks

Aiolos

jessica_webb
Creator III
Creator III
Author

Perfect

Thank you so, so much for your help Aiolos! Really appreciate it.

Can now go home for Christmas without having to worry about my application not working

uacg0009
Partner - Specialist
Partner - Specialist

haha..you're welcome.

It's also glad that I can help you.

Merry Christmas~~

Aiolos Zhao