Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am using below expression
if(Dimensionality()=0,$(vInvTurnsCalc)/$(vDivide),(sum(Cogs_Value_USD)*-1)/If((Divide/12), vBasePrice,((vBasePrice+Sum(InventoryValue_USD))/2))/(Divide/12)*12)
where
vBasePrice=Sum({<Month_Turns = {'Jun'}>}InventoryValue_USD)
it seems that sum within if causing issue.
Data is not displayed in table properly it is showing 0.00 after applying number formatting.
When I can remove the number formatting it is showing as excepted.
Please suggest.
Can you please suggest what would be expression for this .
Thanks.
I think you need to explain in some detail what you are trying to achieve with this calculation. It looks pretty confusing to me and perhaps it could be simplified.
Also, please write the expression without using variables. Let's get that working correctly first, and one its working you can look at which parts can and should be replaced with a variable value or variable expansion.
Hi All,
Can you please suggest how to use sum before if condition here.
Thanks
First bifurcate from long expression to short expression. And check whether the num() format which you define.
Hi All,
pfa.
I HAVE ATTACHED THE SAMPLE FILE.
Isum with if condition is not working it can shows all values as o.oo.
When I select any of part number values is displayes.
Thanks
It's because Denominator issue as you are storing FieldName into variable. Qlikview can not understand the which value need to be calculated for that we have Sum, Count, Max, Avg and Min ...
So, Consider as
Value
12
12
2
And, Expression never return using Sum(Value)/Value // Throws nullify because we are denominate with single field not any aggregate function .. Does it make sense?
Can you please suggest ,what would be the change.Because after removing varialbe and using expr have the same issue.
thanks
Hi all,
Can you please suggest on this.
Thanks.
I think you need to explain in some detail what you are trying to achieve with this calculation. It looks pretty confusing to me and perhaps it could be simplified.
Also, please write the expression without using variables. Let's get that working correctly first, and one its working you can look at which parts can and should be replaced with a variable value or variable expansion.
Not sure how i can suggest you, What are you trying with demonstration?
Hi John,
thanks for the response,
In pivot table expression for total row
if Dimensionality=0 then if(Dimensionality()=0,$(vInvTurnsCalc)/$(vDivide)
where vInvTurnsCalc is sum of the fiels from backend without the rolling sum.
we have divide field for jun =12
july =24
aug=36
sum(Cogs_Value_USD) is summation of three fields from backend.
base price is the price for (InventoryValue_USD) for the month of jun.
we need to divide summation of cogs value by jun price if month if jun
and if month is july then value forbase price would be jun+jul
If((Divide=12), vBasePrice,((vBasePrice+Sum(InventoryValue_USD)
(Divide/12)*12)-this logic is as per tranformation written in excel.
so below is my formula
if(Dimensionality()=0,$(vInvTurnsCalc)/$(vDivide),(sum(Cogs_Value_USD)*-1)/If((Divide=12), vBasePrice,((vBasePrice+Sum(InventoryValue_USD))/2))/(Divide/12)*12)
but it is not working in defauly view shows only 0.00.
thanks