topic Re: Dividing different variable values based on Selection in QlikView

Dividing different variable values based on Selection

Cheng — Sat, 16 Nov 2024 21:36:37 GMT

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?

Thank you in advance for the help!

Re: Dividing different variable values based on Selection

zzyjordan — Mon, 14 Jan 2019 03:39:53 GMT

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

ZZ

Re: Dividing different variable values based on Selection

Cheng — Mon, 14 Jan 2019 04:01:02 GMT

Hi Zzyjordan,

FISCAL YEAR	Sales Period	sum({<[COMPANY ID]={$(40)}>}[VALUE BASE])/[CURRENCY RATE]	Revenue	Company ID
2012	201201	4036.55 / 0.7757	4036.55	40
2013	201301	1589.09 / 0.8239	1589.09	40
2014	201401	….	165.97	40
2015	201501	….	3879.46	40
2016	201601	….	6852.97	40
2017	201701	….	6146.88	40
2018	201801	….	4378.68	40
2019	201901	….	3572.67	40
2015	201501	113.39 / 0.783247	113.39	80
2016	201601	13.46 / 0.543325	13.46	80
2017	201701	….	8.33	80
2018	201801	….	52.96	80
2019	201901	….	95.63	80

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.

Re: Dividing different variable values based on Selection

marcus_sommer — Mon, 14 Jan 2019 16:13:26 GMT

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

Re: Dividing different variable values based on Selection

Cheng — Tue, 15 Jan 2019 01:22:57 GMT

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?

Re: Dividing different variable values based on Selection

Cheng — Tue, 15 Jan 2019 02:35:44 GMT

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)

Re: Dividing different variable values based on Selection

marcus_sommer — Tue, 15 Jan 2019 09:32:17 GMT

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