Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
tester_sogeti
Contributor

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

Re: Agreggation and set analysis with parameters

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

9 Replies

Re: Agreggation and set analysis with parameters

Hi Lluis,

Is it possible to provide the sample app for check?

tester_sogeti
Contributor

Re: Agreggation and set analysis with parameters

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

tester_sogeti
Contributor

Re: Agreggation and set analysis with parameters

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

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

tester_sogeti
Contributor

Re: Agreggation and set analysis with parameters

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

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)

tester_sogeti
Contributor

Re: Agreggation and set analysis with parameters

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

No Problem. Welcome 🙂

tester_sogeti
Contributor

Re: Agreggation and set analysis with parameters

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