if with sum caused an issue

[kishorj1982]

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.

[kishorj1982]

Hi All,

Can you please suggest how to use sum before if condition here.

Thanks

[Anil_Babu_Samineni]

First bifurcate from long expression to short expression. And check whether the num() format which you define.

[kishorj1982]

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

[Anil_Babu_Samineni]

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?

[kishorj1982]

Can you please suggest ,what would be the change.Because after removing varialbe and using expr have the same issue.

thanks

[kishorj1982]

Hi all,

Can you please suggest on this.

Thanks.

[jonathandienst · Accepted solution]

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.

[Anil_Babu_Samineni]

Not sure how i can suggest you, What are you trying with demonstration?

[kishorj1982]

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