topic Re: aggr with variables in QlikView

aggr with variables

ilanbaruch — Thu, 13 Oct 2016 11:01:37 GMT

hi all,

is it possible to aggregate from a variable?

tried it like this

=FirstSortedValue(PAYMENT_TYPE, -Aggr($(Amount$), PAYMENT_TYPE))

but didn't retrieve..

any ideas?

advanced thanks

Re: aggr with variables

sunny_talwar — Thu, 13 Oct 2016 11:02:40 GMT

You should be able to. I am guessing that Amount$ is your variable? How do you define it? I mean what is the expression behind it?

Re: aggr with variables

ilanbaruch — Thu, 13 Oct 2016 11:24:20 GMT

hi Sunny, behind it there are two variables divided:

Amount$ = mApproved / mTotalAmount

Re: aggr with variables

sunny_talwar — Thu, 13 Oct 2016 11:32:02 GMT

Hahaha and how are those defined? Can you share a sample maybe?

Re: aggr with variables

ilanbaruch — Thu, 13 Oct 2016 11:48:07 GMT

mApproved :

sum({$<STATUS={'Approved'}>}AMOUNT)

mTotalAmount:

sum(AMOUNT)

Re: aggr with variables

sunny_talwar — Thu, 13 Oct 2016 11:52:16 GMT

So, can you try to check if this is working or not?

=FirstSortedValue(PAYMENT_TYPE, -Aggr(Sum({<STATUS = {'Approved'}>} AMOUNT)/Sum(AMOUNT), PAYMENT_TYPE))

and if that works, then it is the way you have defined your variables that might be causing an issue. It would be faster if you are able to share a sample, if not, then we will slowly troubleshoot this.

Re: aggr with variables

ilanbaruch — Thu, 13 Oct 2016 12:18:15 GMT

hi,

tried it with the expression but it didn't work.

set the expression in a variable 'vTest' it retrieved the value..

vTest in the aggregate again didn't work...

Re: aggr with variables

sunny_talwar — Thu, 13 Oct 2016 12:21:00 GMT

Tried it with expiration? Not sure I understand what you mean?

Re: aggr with variables

ilanbaruch — Thu, 13 Oct 2016 12:22:28 GMT

expression

Re: aggr with variables

sunny_talwar — Thu, 13 Oct 2016 12:24:41 GMT

I don't really know what you have except the expression you have shared. Can you share some information as to what exactly are you trying to do? May be a sample?

Re: aggr with variables

ilanbaruch — Thu, 13 Oct 2016 12:32:34 GMT

retrieve PAYMENT_TYPE (Name) by the maximum amount (amount)

Re: aggr with variables

sunny_talwar — Thu, 13 Oct 2016 12:37:34 GMT

Then this should work unless you have duplicates

=FirstSortedValue(PAYMENT_TYPE, -Aggr(Sum(AMOUNT), PAYMENT_TYPE))

or you can do this if you have duplicates and you want to pick one name

=FirstSortedValue(DISTINCT PAYMENT_TYPE, -Aggr(Sum(AMOUNT), PAYMENT_TYPE))

or this if you wish to show all PAYMENT_TYPE separated by commas

=Concat(DISTINCT {<PAYMENT_TYPE = {"=Rank(Sum(AMOUNT)) < 2"}>} PAYMENT_TYPE)

Re: aggr with variables

ilanbaruch — Thu, 13 Oct 2016 12:42:06 GMT

!!!duplicates !!!

this was the issue

thank you very much!

Re: aggr with variables

sunny_talwar — Thu, 13 Oct 2016 12:43:01 GMT

Awesome