Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Stock Trend Analysis

Hi Team,

I am trying to display stock trend analysis for a particular item as follows:

stock_Trend_Analysis.png

Filter Year : 2016

Find below expression to calculate same:


stock_trend_expression.png

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.


4 Replies
MVP
MVP

Re: Stock Trend Analysis

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

Not applicable

Re: Stock Trend Analysis

Thank you

Not applicable

Re: Stock Trend Analysis

1. get 2 variables:
 

2. in Measure use 

      Sum({<StockDate = {">=$(vFrom)<=$(vTo)"} >} valueAmended )

Not applicable

Re: Stock Trend Analysis

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)

)