topic Re: Struggling with Calculation - please help ! in QlikView

Struggling with Calculation - please help !

timsaddler — Fri, 24 Jun 2016 09:40:22 GMT

Hi all

My calculation for #StockCover produces zero as a result and I don't understand why?

All other calcs work fine.

What am I missing?

StockCoverPreCalc:
LOAD
BusinessUnit,
GLDate,
ShortItemNo,
ItemDesc,
ItemBU3,
Sum(#SalesQty) as #SalesQty,
Sum(#SalesTns) as #SalesTns,
If(GLDate >AddMonths(monthend(today()),-12) and GLDate <=monthend(today(),-0), Sum(#SalesTns/12)) as #AvgeSalesLast12,
If(GLDate >AddMonths(monthend(today()),-12) and GLDate <=monthend(today(),-0), Sum(#SalesTns/12)
/
If(GLDate = today(),Sum(#StockTonnes_PreCalc))) as #StockCover,
Sum(#StockTonnes_PreCalc) as #StockTonnes_PreCalc

Resident StockCoverPreCalc_tmpA

Group by
BusinessUnit,GLDate,ShortItemNo,ItemDesc,ItemBU3

;

Drop table StockCoverPreCalc_tmpA;

Message was edited by: Tim Saddler Heres a qvw with one product only as this might make it easier to understand the issue

Re: Struggling with Calculation - please help !

Anonymous — Fri, 24 Jun 2016 09:55:36 GMT

Hi, Tim,

Can you pls list the "GLDate" column as well?

Because you use "GLDate = today()" as a condition, something should be wrong here.

Zhihong

Re: Struggling with Calculation - please help !

marcus_sommer — Fri, 24 Jun 2016 11:03:43 GMT

I think today() isn't right as condition and it should look more like:

If(GLDate >AddMonths(monthend(today()),-12) and GLDate <=monthend(today(),-0), Sum(#SalesTns/12)
/
If(GLDate >= monthend(today(), -0),Sum(#StockTonnes_PreCalc))) as #StockCover,

- Marcus

Re: Struggling with Calculation - please help !

PrashantSangle — Fri, 24 Jun 2016 11:23:20 GMT

Hi,

try something like this,

If(GLDate >AddMonths(monthend(today()),-12) and GLDate <=monthend(today(),-0), Sum(#SalesTns/12))

If(GLDate = today(),Sum(#StockTonnes_PreCalc)) as #StockCover,

Regards

Re: Struggling with Calculation - please help !

sunny_talwar — Fri, 24 Jun 2016 11:35:11 GMT

May be if non of the suggestions work, provide a small sample to look at

Re: Struggling with Calculation - please help !

timsaddler — Fri, 24 Jun 2016 11:38:02 GMT

Sorry this still produces zero

Re: Struggling with Calculation - please help !

PrashantSangle — Fri, 24 Jun 2016 11:42:01 GMT

Hi,

do you have value for #StockTonnes_PreCalc for today

Regards

Re: Struggling with Calculation - please help !

timsaddler — Fri, 24 Jun 2016 11:45:29 GMT

Have posted qvw now

Re: Struggling with Calculation - please help !

sunny_talwar — Fri, 24 Jun 2016 11:54:19 GMT

I cannot reload, but I think the issue is related to the numerator. Can you try this:

Sum(If(GLDate >AddMonths(monthend(today()),-12) and GLDate <=monthend(today(),-0), #SalesTns/12))

/If(GLDate = today(),Sum(#StockTonnes_PreCalc)) as #StockCover,

Re: Struggling with Calculation - please help !

timsaddler — Fri, 24 Jun 2016 12:02:36 GMT

Yes there is a value

Re: Struggling with Calculation - please help !

timsaddler — Fri, 24 Jun 2016 12:03:11 GMT

still zero - have posted qvd file of limited data set now

Re: Struggling with Calculation - please help !

effinty2112 — Fri, 24 Jun 2016 12:06:35 GMT

Hi Tim,

This blog entry by Henric Cronström is worth reading

Rounding Errors

Re: Struggling with Calculation - please help !

PrashantSangle — Fri, 24 Jun 2016 12:11:18 GMT

Hi,

debug your script on front end

use 2 text object

in 1 write

If(GLDate >AddMonths(monthend(today()),-12) and GLDate <=monthend(today(),-0), Sum(#SalesTns/12))

in another write

If(GLDate = today(),Sum(#StockTonnes_PreCalc))

Check value is there or not?

If you get value

then create straight table take all group by field as dimenstion and use above expression

check do you get any value??

Regards

Re: Struggling with Calculation - please help !

Anonymous — Fri, 24 Jun 2016 12:32:19 GMT

Hi, Tim,

If you add a GLDate as a column in the table, you will see when the GLDate=today, Sum(#StockTonnes_PreCalc) is not 0, but now Sum(#SalesTns/12)) is 0, that is why #StockCover is 0. Nothing wrong in Qlikview, your logical is wrong here.

Can you check what logical do you want?

Re: Struggling with Calculation - please help !

sunny_talwar — Fri, 24 Jun 2016 12:49:24 GMT

qvd you have attached is the output qvd. It would be helpful to look at the input qvd if possible

Re: Struggling with Calculation - please help !

timsaddler — Fri, 24 Jun 2016 12:53:06 GMT

Hi I have redone the calculation so that GLDate is in the filter and it now calculates.

This works as Sales are over different GL Dates but Stock is always "Today"

StockCoverPreCalc_tmpA:

LOAD BusinessUnit,
//Date(GLDate) as GLDate,
ShortItemNo,
ItemDesc,
ItemBU3,
#SalesTns,
#SalesQty

FROM
[SalesforStockApp.qvd]
(qvd)
Where Match(OrderType,'O1','O3','O6','OU','O7','CO')//Remove Order Type OH
and GLDate >AddMonths(monthend(today()),-12)
and GLDate <=monthend(today(),-0)