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

[JordyWegman]

Hi Chris,

Can you show a sample QVF? Because the way you explain it, it should work.

Jordy

Climber

[wlpike]

Hi Jordy,

this is the qvf file

br

chris

[JordyWegman]

Hi Chris,

What is the content of the variable? Is there a reason why you do this in a chart and not prepare this in the back-end? This seems easier.

Btw, for your enormous wildmatch adventure, you can maybe do this better by making a XLSX file and use a Mapping table. This easier to change (in Excel), easier to apply in Qlik and easier to read for the developer. You know how to?

Jordy

Climber

[wlpike]

Hi Jordy,

the variable contains the calculation of sales data of  two products. 

-> vSumTJ = vSumBJK - vSumAJK

when i add the variable in a KPI field, it shows the value just fine.

But when i want to use it in if clause it does not work.

thanks for the hint for using an xlsx file for mapping. i will keep it in mind for changing it.

br

Chris

[JordyWegman]

Hi Chris,

I'm struggling a bit with the formula, because when you we look at the data, you will only have one row of PRDCTH0CS with BJT and another one with BJK, never both on the same row right? Example (I've deleted all the other columns):

PRDCTH0CS	SALES_1R1
BJK	120
BJT	100

So that means, if you want to subtract something, you need to aggregate. So, based on what are you aggregating? Is that the FSKat for example or FS_UnterKategorie? This is why it is working in a KPI object, but not in a table (guess you used that?).

 Jordy

Climber

[wlpike]

Hi Jordy,

yes thats true, BJT and BJK are never in a row together. 

i want to aggregate the results of two variables. The exact calculation is:

$(vSumBJK) - $(vSumAJK)

Yes i used a table and a master measure with following formula:

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

vSumBJK:

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

vSumAJK:

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

br

Chris

[JordyWegman]

Great, but what is the dimension that you are using? Because that one decides if this is working or not.

Jordy

Climber

[wlpike]

i use the product code (PRDCTH0CS) as dimention.

i want to see all products and the manipulated value of product with code BJK

[JordyWegman]

Following the logic from a few posts back, we see that we can't have BJK and AJK on the same row for the dimension PRDCTH0CS. So we can't execute this in this way.

What can be a possibility is that we load all the data, aggregate over Year/Month/Day, with AJK and again without AJK while you extract this from BJK.

Jordy

Climber