Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
still zero - have posted qvd file of limited data set now
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
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?
qvd you have attached is the output qvd. It would be helpful to look at the input qvd if possible
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)