Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I am trying to display stock trend analysis for a particular item as follows:
Filter Year : 2016
Find below expression to calculate same:
Jan 2016 > it should take all data from stock where transaction date <=31/01/2016
Feb 2016> where transaction date <=28/02/2016
Mar 2016> where transaction date <=31/03/2016
…….
…….
Etc
Grateful if you could kindly advise as above is not working up to my expectation.
Thanking You,
Bharati.
When you say is not working up to your expectation, what is the exact issue you are facing? Error, 0, null, numbers different then expected?
Alternatively, try this:
If(GetSelectedCount(STOJOUCurMonth) = 1, Sum({<STOJOUCurMonth, YearField, QuarterField, MonthYearField, [Transaction Date] = {"$(='<=' & Date(Max([Transaction Date]), 'DD/MM/YYYY'))"}>} Quantity) , Sum(Quantity)
Add all date related fields (in red above) in your set analysis to ignore any selections in them, because any selections you want to make will flow through using the Transaction Date.
Also note that I have not added [STOJOU Item Reference] = {'RM030'} as I was not sure if that was required or not, but feel free to add this wherever you think that it is necessary
Thank you
1. get 2 variables:
2. in Measure use
Sum({<StockDate = {">=$(vFrom)<=$(vTo)"} >} valueAmended )
Get CurMonth = month(YourDateField) in Load scipt
---------------------------
below will display past 12 month Sum
If(GetSelectedCount(CurMonth) = 1 ,
sum( {<
CurMonth = {"$(= monthname(Addmonths(CurMonth,-1)))",
"$(= monthname(Addmonths(CurMonth,-2)))",
"$(= monthname(Addmonths(CurMonth,-3)))",
"$(= monthname(Addmonths(CurMonth,-4)))",
"$(= monthname(Addmonths(CurMonth,-5)))",
"$(= monthname(Addmonths(CurMonth,-6)))",
"$(= monthname(Addmonths(CurMonth,-7)))",
"$(= monthname(Addmonths(CurMonth,-8)))",
"$(= monthname(Addmonths(CurMonth,-9)))",
"$(= monthname(Addmonths(CurMonth,-10)))",
"$(= monthname(Addmonths(CurMonth,-11)))",
// "$(= monthname(Addmonths(CurMonth,-12)))",
"$(= monthname(Addmonths(CurMonth,0)))"
} , trxType={'myType'}
>}
Balance )/12 // this is the Avarage for SalesValue
/ // this is divison
sum( {<
CurMonth = {"$(= monthname(Addmonths(CurMonth,-1)))",
"$(= monthname(Addmonths(CurMonth,-2)))",
"$(= monthname(Addmonths(CurMonth,-3)))",
"$(= monthname(Addmonths(CurMonth,-4)))",
"$(= monthname(Addmonths(CurMonth,-5)))",
"$(= monthname(Addmonths(CurMonth,-6)))",
"$(= monthname(Addmonths(CurMonth,-7)))",
"$(= monthname(Addmonths(CurMonth,-8)))",
"$(= monthname(Addmonths(CurMonth,-9)))",
"$(= monthname(Addmonths(CurMonth,-10)))",
"$(= monthname(Addmonths(CurMonth,-11)))",
// "$(= monthname(Addmonths(CurMonth,-12)))",
"$(= monthname(Addmonths(CurMonth,0)))"
} , trxType={'Sales'}
>}
Balance)/12
,
sum(Balance)
)