Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
timsaddler
Creator III
Creator III

Struggling with Calculation - please help !

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

15 Replies
timsaddler
Creator III
Creator III
Author

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

effinty2112
Master
Master

Hi Tim,

This blog entry by  Henric Cronström is worth reading

Rounding Errors

PrashantSangle

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

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 🙂
Anonymous
Not applicable

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?


sunny_talwar

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

timsaddler
Creator III
Creator III
Author

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)