Qlik sense calculation - Set analysis ?

[#1]

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 :

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

and I aggregate all togather :

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

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.

[swuehl · Accepted solution]

Maybe like this?

=Sum(

Aggr(

Sum("Sales (Local Currency)")

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

,Country, "Contrat Version" )

)

[#3]

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.

[swuehl]

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.

[#5]

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.

[swuehl]

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

[#7]

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.