variable in if clause

[wlpike]

Hi

i have a question concerning if clauses and using variables in it.

i want to change certain sales values for products and would do it in a master measure like this:

if (PRDCTH0CS='BJK',$(vSumJT),SALES_1R1)

so if product code 'BJK' -> return  value of vSumJT, else give normal sales data.

but it returns default value of BJK and not the calculated sum of vSumJT, when showing in table or another chart

vSumJT is a variable defined in chart section.

when selecting vSumJT in a KPI field it does what its told and showing predefined calculated value.

hope anybody can help

thx

br

chris

[wlpike]

you mean it would not work in chart area, but in load script?

i have all calculations done for AJK and BJK in load script, but aggregation of the gap between them is not possible, at least when it tested it.

[JordyWegman]

It can work on every dimension except PRDCTH0CS because this separates the two fields you want to extract from each other.

Jordy

Climber

[wlpike]

but what could i do to calculate the gap between those two products and use it as additional product?

could the definition of variables in load script help? so i could calculate the values of each product and store the new calculation in an additional variable.

and do a mapping afterwards...

[JordyWegman]

You can do this in the front-end and choose any dimension, except PRDCTH0CS, you want and then use:

vSumBJK: Sum({$<PRDCTH0CS={'BJK'}>}SALES_1R1)

vSumAJK: Sum({$<PRDCTH0CS={'AJK'}>}SALES_1R1)

Jordy

Climber 

[wlpike]

i managed to get the sums i need.

i store both variables in a seperate QVD file

LOAD  
	PRDCTH0CS, 
    SALES_1R1,
    [0CALYEAR], 
	[0CALMONTH2], 
	[0CALDAY], 
	[0CALMONTH], 
    
    
    if(PRDCTH0CS='BJK',SALES_1R1,0) as sumBJK,
    if(PRDCTH0CS='AJK',SALES_1R1,0) as sumAJK
    

;

and load it into the qlik app like below.

So i have access to the new calculation and can use it in my charts.

LOAD
    [0CALYEAR], 
	[0CALMONTH2], 
	[0CALDAY], 
	[0CALMONTH], 
    sumBJK,
    sumAJK,
    sumBJK-sumAJK as sumTJT 
FROM [lib://Workshop /BW_Export_20190708_2018only_1.qvd]
(qvd);

[JordyWegman]

Yes good, that is exactly what I meant! Nice work!

Jordy

Climber