Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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
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
I'm not sure that I understood what result should be, but if you want to have a pivot table like:
Product | batchsize | Quantity | QuantityInSingleItems | QuanityFinal |
A | 1 | 5 | 0 | 5 |
B | 6 | 1 | 2 | 8 |
C | 2 | 2 | 1 | 3 |
Then use for 'QuanityFinal' something like: sum(if(batchsize=1, QuantityFieldInBatches, QuantityFieldInUnits)
Regards,
David
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
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
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
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