Evaluate variable for each dimension value

[jessica_webb]

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?

[Anil_Babu_Samineni]

Can we expect any file from you to investigate?

[uacg0009 · Accepted solution]

Hi Jessica,

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

Hope it can help you.

Thanks.

Aiolos

[jessica_webb]

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]

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]

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.

[uacg0009]

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]

Definitely closer!! Thank you

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

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

[uacg0009]

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]

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]

haha..you're welcome.

It's also glad that I can help you.

Merry Christmas~~

Aiolos Zhao