QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Contributor II

if with sum caused an issue

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.

Can you please suggest what would be expression for this .

Thanks.

1 Solution

Accepted Solutions
MVP

Re: if with sum caused an issue

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
12 Replies
Contributor II

Re: if with sum caused an issue

Hi All,

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

Thanks

Re: if with sum caused an issue

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor II

Re: if with sum caused an issue

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

Re: if with sum caused an issue

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor II

Re: if with sum caused an issue

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

thanks

Contributor II

Re: if with sum caused an issue

Hi all,

Can you please suggest on this.

Thanks.

MVP

Re: if with sum caused an issue

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: if with sum caused an issue

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor II

Re: if with sum caused an issue

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