topic Agreggation and set analysis with parameters in QlikView

Agreggation and set analysis with parameters

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

Hi all !!

I've been facing a problem for several days and I am completly stuck! Maybe you can bring a bit of light to the matter.

I have a table with some dimensions, the one that matters now is [ID]. If ID is a certain value I want to call a variable. Following you can see it:

VARIABLE:

weightedmean=sum({<$1,$2,$3,$4,$5,$6,$7,$8 >} Aggr(DISTINCT Csat_Target * Weights, link_targets)) / sum({<$1,$2,$3,$4,$5,$6,$7,$8 >} Aggr(DISTINCT Weights, link_targets) )

EXPRESSION:

if(ID=4,$(weightedmean(FY={'FY17-Q1'} ) ) )

If instead i use the expression without the If condition, the variable is doing calculations, but with it only returns 0.

Somehow, this is not working, at all. I've been looking for workarounds but I am working in a complex QV document and it should be done in this way.

I will provide further information or any kind of extra help you need.

Thanks for the help!

May The Qlik be with you

Mensaje editado por: Lluis Cierco

Re: Agreggation and set analysis with parameters

settu_periasamy — Fri, 24 Nov 2017 13:45:27 GMT

Hi Lluis,

Is it possible to provide the sample app for check?

Re: Agreggation and set analysis with parameters

Anonymous — Fri, 24 Nov 2017 13:51:42 GMT

I can not provide the original but i am making one with sample data

Re: Agreggation and set analysis with parameters

Anonymous — Fri, 24 Nov 2017 14:07:23 GMT

Just edited the original post to add the sample

Hope it helps! Can not understand what is going on :S

Re: Agreggation and set analysis with parameters

settu_periasamy — Fri, 24 Nov 2017 14:20:06 GMT

Can you try to add the 'ID' into the variable and check?

Variable :

weightedmean =

sum({<$1>} Aggr(DISTINCT Csat_Target * Weights, link_targets,ID)) / sum( {<$1>} Aggr(DISTINCT Weights, link_targets,ID) )

Re: Agreggation and set analysis with parameters

Anonymous — Fri, 24 Nov 2017 14:23:16 GMT

Nice!! It worked out!!

Thanks a lot!

I spent a lot of time on it and couldn't happen to think on this!

Good job

Re: Agreggation and set analysis with parameters

settu_periasamy — Fri, 24 Nov 2017 14:42:34 GMT

Instead of the above variable, may be you can try this one for the variable

Aggr(distinct sum({<$1>}Csat_Target * Weights) / sum({<$1>}Weights), ID)

Re: Agreggation and set analysis with parameters

Anonymous — Fri, 24 Nov 2017 15:06:56 GMT

Thanks for the improvement and interest!!

This works fine as well.

In my very particular case (not the sample but the original) I can't apply this becose i need to make the sums only once for each linked_targed. It is complicated to explain here all, but I just wanted to point out the 1st option have this difference from this one.

(In my particular case each link_targets has many duplicates of the weights and this causes error in the calculations, the 1st solution proposed is the one that can accomplish this).

Thanks again for the help! And nice idea

Re: Agreggation and set analysis with parameters

settu_periasamy — Fri, 24 Nov 2017 15:13:20 GMT

No Problem. Welcome 🙂

Re: Agreggation and set analysis with parameters

Anonymous — Fri, 24 Nov 2017 16:20:12 GMT

Sorry to ask you again but at the moment of impement it in the structure of my QV it is not working. (I have to follow this structure becose many things depend from this, old qlikview file that someone did a long time ago and no one understands).

Thanks for the help ^^

Aggr and variables