topic Re: Evaluate variable for each dimension value in QlikView

Evaluate variable for each dimension value

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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:

	One	Two	Three	Four
EXP:	47.9 (A-)	()	()	()

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

	One	Two	Three	Four
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?

Re: Evaluate variable for each dimension value

sunny_talwar — Wed, 20 Dec 2017 13:37:44 GMT

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?

Re: Evaluate variable for each dimension value

jessica_webb — Wed, 20 Dec 2017 13:41:38 GMT

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...

Re: Evaluate variable for each dimension value

sunny_talwar — Wed, 20 Dec 2017 13:45:05 GMT

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

Re: Evaluate variable for each dimension value

jessica_webb — Wed, 20 Dec 2017 13:47:04 GMT

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

Re: Evaluate variable for each dimension value

Anil_Babu_Samineni — Wed, 20 Dec 2017 13:50:05 GMT

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))

Re: Evaluate variable for each dimension value

sunny_talwar — Wed, 20 Dec 2017 13:55:05 GMT

Try this

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

Re: Evaluate variable for each dimension value

jessica_webb — Wed, 20 Dec 2017 16:11:48 GMT

No joy there unfortunately!

That gives me:

	One	Two	Three	Four
EXP		()	()	()

Re: Evaluate variable for each dimension value

jessica_webb — Wed, 20 Dec 2017 16:13:08 GMT

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

I just get:

	One	Two	Three	Four
EXP		()	()	()

Re: Evaluate variable for each dimension value

sunny_talwar — Wed, 20 Dec 2017 16:23:18 GMT

What do you get for this alone? Anything?

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

Re: Evaluate variable for each dimension value

Anil_Babu_Samineni — Thu, 21 Dec 2017 01:13:01 GMT

Can we expect any file from you to investigate?

Re: Evaluate variable for each dimension value

uacg0009 — Thu, 21 Dec 2017 07:18:15 GMT

Hi Jessica,

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

Hope it can help you.

Thanks.

Aiolos

Re: Evaluate variable for each dimension value

jessica_webb — Thu, 21 Dec 2017 08:13:50 GMT

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

Re: Evaluate variable for each dimension value

jessica_webb — Thu, 21 Dec 2017 08:14:52 GMT

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

Re: Evaluate variable for each dimension value

jessica_webb — Thu, 21 Dec 2017 08:17:04 GMT

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

Now I have:

	One	Two	Three	Four
EXP	51.45 (A)	51.45 ()	()	()

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

Re: Evaluate variable for each dimension value

uacg0009 — Thu, 21 Dec 2017 08:27:54 GMT

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

Thanks

Aiolos

Re: Evaluate variable for each dimension value

jessica_webb — Thu, 21 Dec 2017 08:48:11 GMT

Definitely closer!! Thank you

	One	Two	Three	Four
EXP	47.9 (A-)	55 ()	()	()

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

Re: Evaluate variable for each dimension value

uacg0009 — Thu, 21 Dec 2017 08:55:25 GMT

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

Re: Evaluate variable for each dimension value

jessica_webb — Thu, 21 Dec 2017 09:04:07 GMT

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

Re: Evaluate variable for each dimension value

uacg0009 — Thu, 21 Dec 2017 09:10:20 GMT

haha..you're welcome.

It's also glad that I can help you.

Merry Christmas~~

Aiolos Zhao