Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Cheng
New Contributor

Dividing different variable values based on Selection

Hi Gurus,

I am new to Qlikview and I am really confused with the set analysis,

I have 
sum({<[COMPANY ID]={$(40)}>}[VALUE BASE])/[CURRENCY RATE]

How do I choose the currency rate that is corresponding to letter U when company id is 40 (what i select)
I will then obtain the value in my base currency
So basically,  I am dividing all my values by 0.7333 for this case.

Also I need to do this for a few company id, how do i make it automatically divide by different currency rate based on company ids within the expression?

pastedImage.png

Thank you in advance for the help!

Labels (2)
6 Replies
zzyjordan
Contributor II

Re: Dividing different variable values based on Selection

Hi, Cheng
Can you provide some sample data and describe your expected result?

ZZ
Cheng
New Contributor

Re: Dividing different variable values based on Selection

Hi Zzyjordan,

FISCAL YEARSales Periodsum({<[COMPANY ID]={$(40)}>}[VALUE BASE])/[CURRENCY RATE]RevenueCompany ID
20122012014036.55 / 0.77574036.5540
20132013011589.09 / 0.82391589.0940
2014201401….165.9740
2015201501….3879.4640
2016201601….6852.9740
2017201701….6146.8840
2018201801….4378.6840
2019201901….3572.6740
2015201501113.39 / 0.783247113.3980
201620160113.46 / 0.54332513.4680
2017201701….8.3380
2018201801….52.9680
2019201901….95.6380

pastedImage1.png

U = 40, H = 80 for instance.

I want to filter by company id (40 or 80) and obtain the value of the revenue in base currency for instance by summing the values.

MVP & Luminary
MVP & Luminary

Re: Dividing different variable values based on Selection

If you have such currency-table which is associated with PERIOD and Currency-ID respectivel Company-ID to the sales-table you don't need a set analysis for the company else the following will be enough:

sum([VALUE BASE])/[CURRENCY RATE])

and if you want to use without the lower granularity, for example within a KPI you need an aggr-wrapping for it like:

sum(aggr(sum([VALUE BASE])/[CURRENCY RATE]), PERIOD, [Company ID]))

- Marcus

Cheng
New Contributor

Re: Dividing different variable values based on Selection

currently, i dont have a table that links company id with the foreign currency table, therefore i think i still have to write in the script to ensure that i am dividing only by a particular foreign currency for a particular company id.

My actual currency conversion table contains multiple currency rate for the same sales period instead of just U. (if you refer to the first picture in my first post)
If i just use sum([VALUE BASE])/[CURRENCY RATE]), i dont think the values will be correct.

Do you have any suggestions for this?
Cheng
New Contributor

Re: Dividing different variable values based on Selection

what i want to achieve is similar to this

if (company id = a, we take the sum(value/currencyrate=p)
if (company id = b, we take the sum(value/currencyrate=q)
MVP & Luminary
MVP & Luminary

Re: Dividing different variable values based on Selection

In each case you will need to define a logic to link your information and to grab them from there - if you don't have it in a table you should create one because it will be easier as doing it within the UI within a lot of if-constructs.

- Marcus