Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
wlpike
Contributor III
Contributor III

variable in if clause

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

 

 

 

 

Labels (3)
15 Replies
JordyWegman
Partner - Master
Partner - Master

Hi Chris,

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

Jordy

Climber

Work smarter, not harder
wlpike
Contributor III
Contributor III
Author

Hi Jordy,

 

this is the qvf file

 

br

chris

JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
wlpike
Contributor III
Contributor III
Author

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
Partner - Master
Partner - Master

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):

PRDCTH0CSSALES_1R1
BJK120
BJT100

 

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

Work smarter, not harder
wlpike
Contributor III
Contributor III
Author

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
Partner - Master
Partner - Master

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

Jordy

Climber

Work smarter, not harder
wlpike
Contributor III
Contributor III
Author

i use the product code (PRDCTH0CS) as dimention.

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

JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder