Use of Variable & Expression for Comparison

Sun, 10 Aug 2014 19:46:52 GMT

Hi All,

So here is a situation...

1) I have a dataset that has last 2 quaters data for now (will have close to 6 - 8 quarters data in future) and would like to do a comparison depending on what user wants to look at i.e. there are 4 inputboxes that would allow user to compare data from any of the previous quarters. User can choose to enter either 2, 3 or all 4 input boxes for comparison. (THIS PART IS DONE - INPUT BOX CREATED AND CORRESPONDING VARIABLES HAVE ALS BEEN DEFINED WHERE THE VALUES ARE STORED AND WILL BE USED IN EXPRESSION LATER ON).

User Input Variables :

vCur_Val = 2014Q2

vPrev_Val1 = 2013Q4

vPrev_Val2 = 2013Q2

vPrev_Val3 = 2012Q4

2) The target report that is required to be published has a specific format which I am trying to replicate in QlikView Pivot structure.

eg.

Sample format of the traget report (Last column (Calculations) is not the part of actual report but to explain how the proportions are being calculated)

Items	Amounts 2014Q2	Amounts 2013Q4	Proportions 2014Q2	Proportions 2013Q4	Calculation
GL	500,000	550,000	-	-	No Calculation for Proportion
CL	250,000	255,000	50.00%	46.36%	CL/GL >> (CL as proportion on GL)
GB	50,000	60,000	10.00%	10.91%	GB/GL
CB	30,000	33,000	60.00%	55.00%	CB/GB
BD	2,500	2,500	1.00%	0.98%	BD/CL
OPX	12,500	16,250	2.50%	2.50%	OPX/GL

The report in qlikview till the amounts column has been created and amounts get populated for comparison based on user inputs.

Problem part in the project :

Next step is to create the proportions field where it is getting a bit tricky.

This is what I tried -

- Created variables to fetch the sum of each item to be used in expression later.

eg. of calculation performed in variables

GB as proportion of GL:

vGBProp >> Sum({$<[Items]={'GB'}>} [Amounts]/1000) / Sum({$<[Items]={'GL'}>} [Amounts]/1000)

BD as proportion of CL:

vBDProp >> Sum({$<[Items]={'BD'}>} [Amounts]/1000) / Sum({$<[Items]={'CL'}>} [Amounts]/1000)

- In next step I tried to populate the variable calculation in Pivot using the following formula -

For Current valuation proportions:

if([val_qtr]=vCur_Val AND [Items] = 'GB', vGBProp,

if([val_qtr]=vCur_Val AND [Items] = 'BD', vBDProp,

If([val_qtr]=vCur_Val AND [Items] = other item, some calculation, and so on...

It doesn't calculate anything with the above formula. I would need similar expression for the other user input variables for comparative analysis.

Is there any way I can achieve this ?? Getting the proportions of different quarters based on user inputs ??

Please advice.

Thanks & Regards

Vinay Gera

Re: Use of Variable & Expression for Comparison

Mon, 11 Aug 2014 17:57:37 GMT

Can anyone please suggest something on this one. This is really urgent.

Thanks!

Re: Use of Variable & Expression for Comparison

marcus_sommer — Mon, 11 Aug 2014 19:32:26 GMT

If you want calculate inside a dimension-value the values from an another dimension-value you need to tell it the expression, instead of:

Sum({$<[Items]={'GB'}>} [Amounts]/1000) / Sum({$<[Items]={'GL'}>} [Amounts]/1000)

try this:

Sum([Amounts]) / Sum({1<[Items]={'GL'}, [val_qtr] = {$(vPrev_Val1)}>} [Amounts]) /1000

Alternatively you could use above().

Here it seemed to miss the $-expansion:

For Current valuation proportions:

Instead:

if([val_qtr]=vCur_Val AND [Items] = 'GB', vGBProp,

....

try this:

if([val_qtr]=vCur_Val AND [Items] = 'GB', $(vGBProp),

....

For such a nested if you could also use pick(match()):

pick(match([Items], 'GL', 'GB', ....), Expression1, Expression2, ....)

I hope it is helpful.

- Marcus

Re: Use of Variable & Expression for Comparison

Wed, 03 Sep 2014 09:39:05 GMT

Hi Mark,

ThankYou so much for your answer. Pick & Match was not the exact solution for the query. Howver, it did give a direction which solved teh problem :).

Thanks!

topic Re: Use of Variable & Expression for Comparison in QlikView

Use of Variable & Expression for Comparison

Re: Use of Variable & Expression for Comparison

Re: Use of Variable & Expression for Comparison

Re: Use of Variable & Expression for Comparison