Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Qlik sense calculation - Set analysis ?

Hi all,

I have a need on qliksense : doing simulation.

Let me explain :

I have one contract with different version (one per month), in this contrat my exchange rate and sales have variations. And this contrat is based on 2 countries.

What I need to do is simulating the exchange rate on all contract version based on one of version.

One example :

I want to apply the exchange rate coming from the version 1 for USA and UK on corresponding sales :

This is my set of data :

data_sample 1.png

I keep the rate from version 1 and I apply it on all sales.

data_sample 2.png

and I aggregate all togather :

This is the result I need with the exchange rate of Version 1:

data_sample 3.png

In qliksense I'm trying to do that with set analysis but it doesn't works well because the value doesn't split correctly through Versions usgin this : ({$<"Contrat Version"={'Version 4'}>}"xrate euro") .. I don't know how to calculate it with differents level of aggregation.

Any idea to resolve that ?

you will find attached a dashboard sample with this set of data and the data set

Thank you.

1 Solution

Accepted Solutions
MVP
MVP

Re: Qlik sense calculation - Set analysis ?

Maybe like this?

=Sum(

Aggr(

Sum("Sales (Local Currency)")

*Only(Total<Country>{<[Contrat Version]= {'Version 4'}>}[xrate euro])

,Country, "Contrat Version" )

)

6 Replies
MVP
MVP

Re: Qlik sense calculation - Set analysis ?

Maybe like this?

=Sum(

Aggr(

Sum("Sales (Local Currency)")

*Only(Total<Country>{<[Contrat Version]= {'Version 4'}>}[xrate euro])

,Country, "Contrat Version" )

)

Not applicable

Re: Qlik sense calculation - Set analysis ?

Yep ! this is working well !!

And in the same way I'm trying to do the same thing but Fixing the Sales (Local Currency) and apply the current xrate.

I'm trying this :

=Sum(

Aggr(

Only(Total<Country>{<[Contrat Version]= {'Version 4'}>}"Sales (Local Currency)") ,Country, "Contrat Version" )

* [xrate euro]

)


But doesn't seems to works.


any idea ?

C.

MVP
MVP

Re: Qlik sense calculation - Set analysis ?

I think you are close:

Sum(

Aggr(

Sum(Total<Country>{<[Contrat Version]= {'Version 4'}>}"Sales (Local Currency)")* Only([xrate euro])

,Country, "Contrat Version" )

)

The xrate and sales aggregations need to be put in both into the advanced aggregation inner aggregation.

Not applicable

Re: Qlik sense calculation - Set analysis ?

Whaou :-) nice !!!

thanks a lot :-)

If I want the Sale from version 4 and rate from version 5 :

I don't see another to do this than that :

Sum(

Aggr(

Sum(Total<Country>{<[Contrat Version]= {'Version 4'}>}"Sales (Local Currency)")

*

Only(Total<Country>{<[Contrat Version]= {'Version 5'}>} [xrate euro])

,Country, "Contrat Version" )

)

It works only for row Version 4 and 5 ... but not all.

MVP
MVP

Re: Qlik sense calculation - Set analysis ?

That's because the two set expressions are limiting the scope of the aggr() dimensions. Add an aggregation in default set that returns zero (because you don't want to modify the total sum).

Sum( Aggr(

Sum(Total<Country>{<[Contrat Version]= {'Version 4'}>}"Sales (Local Currency)")

* Only(Total<Country>{<[Contrat Version]= {'Version 5'}>} [xrate euro])

+Sum( 0)

,Country, "Contrat Version" ) )

Not applicable

Re: Qlik sense calculation - Set analysis ?

whaou, I would never found that I understand way better set analysis now.

Thanks a lot swuehl for your help ! end users will be happy !

C.