4 Replies Latest reply: Jun 22, 2014 12:47 AM by Sundarakumar Kalaimani RSS

    Variable in set analysis

      Hi All,


      I have a variable:


      Date(AddMonths(min({<[Fcst Version]={"$(=Maxstring(Fcst_Num))"}>}[Fcst Month]),-1),'MMM-YY')


      This determines the Minimum month of the maxstring of the fcst Version.


      Now I have a set analysis that I need to include the variable.


      Sum({<MonthYear={insert variable here}>}Fcst Qty)


      How can I apply that?


      My current set analysis does not work:


      Sum({<MonthYear={$vMinFcstMonth}>} Fcst Qty)



        • Re: Variable in set analysis
          Sundarakumar Kalaimani

          try using

          sum({<[Month Year]={'$(=vMinFcstMonth)'}>}[Fcst Qty])


          pls check first if the variable is returning the required value with a text box



            • Re: Variable in set analysis
              Sundarakumar Kalaimani

              if this doesn't work,

              put an equal sign before the variable in variable overview

              check if it is returning the year month intended by putting it in a text box

              if it is ok

              use expression as

              sum({<[Month Year]={$(vMinFcstMonth)}>}[Fcst Qty])

              this should work.



                • Re: Variable in set analysis

                  Yes this one works.. My other question is how to create a comparison between a variable in a set analysis.


                  ex: vMinMonth = Apr-14


                  sum({<[Month Year]={">=$(=Min([Fcst Month]))<=$($(vMinMonth))"}>}[Fcst Qty])

                  // what I want sum({<[Month Year]=Mar-14<=Apr-14>}

                  on my part this does not produce the result I wanted

                    • Re: Variable in set analysis
                      Sundarakumar Kalaimani

                      I would prefer to change the data to its number format when u want to implement such time ranges.

                      Because interpreting the number is faster and easier. though this can be done with the time fields too.

                      num(date) would return the number format of the date. have this as a field in ur back end. use this new field in ur set analysis. for eg if u want to see the data of last one month u would use it as


                      create variable like vtoday=num(today()) and vlastmonthdate=num(today()-30).

                      Like this u can create what ever time range u wish to filter.

                      Pls specify the business rule of what time range u need so that we can help u in the same.


                      Hope this helps.