Solved: Struggling with Calculation - please help ! - Qlik Community

timsaddler · ‎2016-06-24

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

timsaddler · ‎2016-06-24

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)

View solution in original post

Anonymous · ‎2016-06-24

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

marcus_sommer · ‎2016-06-24

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

PrashantSangle · ‎2016-06-24

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

sunny_talwar · ‎2016-06-24

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

timsaddler · ‎2016-06-24

Sorry this still produces zero

PrashantSangle · ‎2016-06-24

Hi,

do you have value for #StockTonnes_PreCalc for today

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

timsaddler · ‎2016-06-24

Have posted qvw now

sunny_talwar · ‎2016-06-24

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,

timsaddler · ‎2016-06-24

Yes there is a value