Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Dynamic field names without if

Hello!

I need some input for the following scenario.

We have articles, which are sold in varying batch sizes. e.g. single bottles, batch of 6 bottles etc.

Field: BatchSize

Summing up the sold numbers of different batch sizes doesn't make sense. What does 1 batch of 6 bottles plus 2 bottles equate to? 3? 8? You know what I mean...

Still, when only one batch size is selected we want to show the number of sold batches, not of single bottles. So we created two fields, one for the sold number in single bottles (QuantityInSingleItems) and one for the batches (Quantity).

Condition:

LET $OnlyOneBatchSize = count(distinct BatchSize) = 1;

Dynamic field formula:

LET $QuantityField = $(=if(

    $(OnlyOneBatchSize),

    'Quantity',

    'QuantityInSingleItems'

))

So we use in the chart:

sum($(QuantityField))

But this (as far as I understand it right now) will result in if-evaluations in every row. I thought it would be evaluated once, due to the several levels of $ sign expansion/evaluation, but doesn't seem to be the case. We could split the formula into two and use $OnlyOneBatchSize as a condition for the visibility, but there are two more conditions of this kind and that would result in a whole lot of formulas. Macros for setting the condition variables are something, which I'd like to avoid for reasons of maintenability.

Please advice and hint on best practices. Right now (with a few those if-this-then-that-field-else-the-other-field) formulas in the application, the responsiveness has suffered quite a bit.

Regards,

Sandro

1 Solution

Accepted Solutions
maxgro
MVP
MVP

I think you can set OnlyOneBatchSize to 0 (n batch size) or 1 (1 batch size) and then use

sum(Quantity)*$(OnlyOneBatchSize) + sum(QuantityInSingleItems)*(1-$(OnlyOneBatchSize))

see here for an explanation of variable evaluation

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables

and also look at the last example in the same link

View solution in original post

14 Replies
Not applicable
Author

Hey Sandro,

Are these variable expressions on the front-end. Why dont you use the same logic for a calculated field in the back-end.

Do the calculations on the back-end as much as possible will improve performance.

I am guessing you might have to use Group By function but its definitely possible.

Anonymous
Not applicable
Author

Hello Ajay!

Thank you for your response 🙂

We have a missunderstanding here. It's not that some of the articles are sold in only one batch size and others in many. They pretty much all have different batch sizes. The determinant should be how many are in the current selection. So the user can choose whether he wants to see sales for all batch sizes (which should then result in sum(QuantityInSingleItems) or focus on one of them (which should then result in sum(Quantity))

So yes, the expressions have to be in the front-end somehow

Regards,

Sandro

daveamz
Partner - Creator III
Partner - Creator III

Hi Sandro,

For Quantity to make sense you can use in script something like:

LOAD *

          , Quantity+QuantityInSingleItems/BatchSize as QuantityField

then you should have for 1 batch of 6 bottles plus 2 bottles something like 1 + 2/6 = 1.3333333 batches.

so you can use in front end : sum(QuantityField)

If you want your Quantity field in bottles then use: Quantity*BatchSize + QuantityInSingleItems => 1*6+2 = 8 bottles.

Regards,

David

Anonymous
Not applicable
Author

Hello David!

Thank you for your input.

I can see how I could unite two different batch sizes in one quantiy with your calculations. We are doing something similar already...

But we'd like to have one (QuantityInSingleItems) at one point and the other (Quantity) at other times. With your approach I would still have to decide beforehand which number we want to show.

Are there any conditions under which an if statement won't be processed for every single row, but only once for the entire diagramm? Like when the statement is operating not along the dimensions of the diagramm? At some point I thought that nesting the if statement in an expression, which I then expand will, avoid the above. Am I right or wrong on that assumption?

Regards,

Sandro

daveamz
Partner - Creator III
Partner - Creator III

I'm not sure that I understood what result should be, but if you want to have a pivot table like:

ProductbatchsizeQuantityQuantityInSingleItemsQuanityFinal
A1505
B6128
C2213

Then use for 'QuanityFinal' something like: sum(if(batchsize=1, QuantityFieldInBatches, QuantityFieldInUnits)

Regards,

David

maxgro
MVP
MVP

I think you can set OnlyOneBatchSize to 0 (n batch size) or 1 (1 batch size) and then use

sum(Quantity)*$(OnlyOneBatchSize) + sum(QuantityInSingleItems)*(1-$(OnlyOneBatchSize))

see here for an explanation of variable evaluation

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables

and also look at the last example in the same link

Anonymous
Not applicable
Author

You are right, the desired result hasn't been properly specified.

Article A has batch sizes 1 and 6. On a specific day 10 of batch size 1 and 10 of batch size 6 have been sold.

Selection I)

Article A, no batch size selected

We thus have two different batch sizes and want to display in single items

Article     |     Quantity

A            |     70

The 70 is the total sales in single items 10 * 1 + 10 * 6 = 70

Selection II)

Article A, batch size 6 is selected

We thus have only one batch size and want to display the actual quantity

Article     |     Quantity

A            |     6

The 6 is the total sales in the selected batch size

Does that make it clear? 🙂

Sandro

Anonymous
Not applicable
Author

Hello Massimo!

Interesting angle you have there 🙂

I would have to set the variable OnlyOneBatchSize to 0 or 1 by a macro, right?

Then I believe we could simplify again to sum($(QuantityField)) with QuantityField being set to 'Quantity' or 'QuantityInSingleItems' by a macro respectively. Simpler formula and same "no row based evaluation necessary" result. Correct?

Sandro

maxgro
MVP
MVP

why a macro?

create an input box

add the variable OnlyOneBatchSize

with expression      =if(count(distinct  BatchSize)>1,0,1)              (remember 😃

you should see a 1 or 0 in the input box depending on selection

use the expression

sum(Quantity)*$(OnlyOneBatchSize) + sum(QuantityInSingleItems)*(1-$(OnlyOneBatchSize))


if I'm not  wrong at the row level (chart) you're executing a

sum(Quantity)*1 + sum(QuantityInSingleItems)*(1-1)

or

sum(Quantity)*0 + sum(QuantityInSingleItems)*(1-0)

there isn't any if