topic Re: if with sum caused an issue in QlikView

if with sum caused an issue

kishorj1982 — Thu, 07 Dec 2017 13:44:58 GMT

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.

Re: if with sum caused an issue

kishorj1982 — Thu, 07 Dec 2017 16:06:27 GMT

Hi All,

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

Thanks

Re: if with sum caused an issue

Anil_Babu_Samineni — Thu, 07 Dec 2017 16:17:29 GMT

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

Re: if with sum caused an issue

kishorj1982 — Fri, 08 Dec 2017 03:55:56 GMT

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

Anil_Babu_Samineni — Fri, 08 Dec 2017 04:04:08 GMT

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

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?

Re: if with sum caused an issue

kishorj1982 — Fri, 08 Dec 2017 04:26:59 GMT

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

thanks

Re: if with sum caused an issue

kishorj1982 — Fri, 08 Dec 2017 05:10:52 GMT

Hi all,

Can you please suggest on this.

Thanks.

Re: if with sum caused an issue

jonathandienst — Fri, 08 Dec 2017 05:48:40 GMT

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.

Re: if with sum caused an issue

Anil_Babu_Samineni — Fri, 08 Dec 2017 05:49:12 GMT

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

Re: if with sum caused an issue

kishorj1982 — Fri, 08 Dec 2017 06:34:45 GMT

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

Re: if with sum caused an issue

kishorj1982 — Fri, 08 Dec 2017 07:42:12 GMT

sry man but this did not work f9.not getting the exact results.

Re: if with sum caused an issue

kishorj1982 — Fri, 08 Dec 2017 12:07:11 GMT

Hi All,

Can you please suggest on this.

Thanks

Re: if with sum caused an issue

Anil_Babu_Samineni — Fri, 08 Dec 2017 12:31:35 GMT

I haven't suggested anything here. Not sure what you mean by "not getting the exact results"