Inventory Closing

[kaldubai]

Hello Qlikers,

I have this problem and i feel a little twist can solve it all for me

so i have created this report where the user is allowed to pass the date and the formula will calculate the inventory for whats equal or less than the passed date .

now what i want to show is the below.

Jan 18	Feb 18	Mar 18
Inventory Value	Inventory Value	Inventory Value

Jan 18 will take what ever is equal or below 31 as Jan Inventory

and the same for Feb and so forth.

See Attached Simple Sample.

Thanks In Advance.

[sohb2015]

Hi,

Can you explain the issue you are facing. That will be helpful.

Thanks

[robert99]

Hi

"where the user is allowed to pass the date"

Do you mean selects a date?

If you do I've done this using MonthAgo (refer blog on this)

sum ({<MonthsAgo = { ">=$(=MonthsAgo)"  }, AuditMthYr  >}

Audit_Qty* Stock_Cost_Price)

So user selects the appropriate MonthsAgo.

If you want the previous month

sum ({<MonthsAgo = { ">=$(=MonthsAgo+1)"  }, AuditMthYr  >}

Audit_Qty* Stock_Cost_Price)

AuditMthYr = MonthYear

[kaldubai]

Thanks for your feedback

i have read about months ago its an intersting concept but i guess its not what im looking for .

to simply put it

i have an expression that  will give me the inventory as of any date (Only on that specific Date)

what i need is :

user will select a Year

then data should show for  all the months on that year.

[robert99]

Relative Calendar Fields

I use thsi blog to set up MonthsAgo in script

12*(Year(Today())-Year(Date)) + Month(Today()) - Month(Date) as MonthsAgo,

Then use this to give a CLOSING INVENTORY BALANCE for the selected month and say three (or whatever) previous months

But there are a number of ways to do this

[robert99]

"what i need is :

user will select a Year

then data should show for  all the months on that year."

Then you may need to get this all set up in script as shown below. (unless someone else knows of a better way)

One example

//needed for peek

SUM2:

//join (AUDIT)

Load

Site_Num ,

//PartNum ,

LinkDate ,

sum (Audit_Qty) as AudQtySumSite ,    //qty

SUM (AuditQtyTimesSkVal) AS AudValSumSite    //Value

==========================================================

// Its easy to produce a report showing movements every month and a total ending balance for all perios selected

// But I was required to produce a report showing the accumulating closing balance by site at month end

// this was how it was done.

AUDIT2:

LOAD

'Yes' as PeekGroup2 ,

Site_Num ,

//PartNum ,

LinkDate ,

If(Site_Num = Peek(Site_Num) ,

RangeSum(Peek('MthAcumSite') ,AudQtySumSite),

AudQtySumSite ) AS MthAcumSite ,

If(Site_Num = Peek(Site_Num) ,

RangeSum(Peek('AudValAcumMthSite') ,AudValSumSite ),

AudValSumSite  ) AS AudValAcumMthSite

Resident SUM2

Order by Site_Num ASC , LinkDate ASC ;