Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Agreggation and set analysis with parameters

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

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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

View solution in original post

9 Replies
settu_periasamy
Master III
Master III

Hi Lluis,

Is it possible to provide the sample app for check?

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Just edited the original post to add the sample

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

settu_periasamy
Master III
Master III

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
Not applicable
Author

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
Master III
Master III

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
Not applicable
Author

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
Master III
Master III

No Problem. Welcome 🙂

Anonymous
Not applicable
Author

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