Agreggation and set analysis with parameters

[Anonymous]

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

[settu_periasamy]

Hi Lluis,

Is it possible to provide the sample app for check?

[Anonymous]

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

[Anonymous]

Just edited the original post to add the sample

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

[settu_periasamy · Accepted solution]

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

Like

Variable :

weightedmean =

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

[Anonymous]

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

[settu_periasamy]

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)

[Anonymous]

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

[settu_periasamy]

No Problem. Welcome 🙂

[Anonymous]

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