5 Replies Latest reply: Sep 7, 2010 3:28 PM by ivan_cruz RSS

    Set Analysis Max Date

    Shumail Hussain

      Can we get the amount as per max invoice date if the user selection of invoice date varies
      for eg if i select specific range for invoice date i.e.31-jul-2010 to 31 jan 2010 then is that possible in set analysis that i can get the amount for 31-jul-2010. whereas if we remove the selection we have 31-aug-2010 as a max invoice date.

      Currently i am doing it mannually which is not giving me the required result by changing the selection. i.e max invoice date

       



      Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'}, Invoice_Date = {'31/07/10'}>} Sales_Amount)


      Shumail

        • Set Analysis Max Date
          Neil Miller

          Something like:

          Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'},
          Invoice_Date = {'$(=Max(Invoice_Date))'}>} Sales_Amount)


          Is Aug 31st the max date in your data set? If nothing is selected, then this will return for the max date in your data set. If there is a date selection, it will be the max date in that selection.

            • Set Analysis Max Date
              Shumail Hussain

              Miller,

              I tried the same code as above but it is not working. I want to tell u one more thing that the invoice_date is in numeric format i.e. 40421 = 31-aug-2010, 40390 = 31-jul-2010.. I am not understanding why the max function is not working here. Any Idea???

              Shumail

                • Set Analysis Max Date

                  FirstSortedValue() might work for you here.

                  • Set Analysis Max Date
                    Neil Miller

                    Try setting up a straight table chart with your expression, but don't give it a label. Then when the chart is rendered, your label will be the expression with the dollar sign expansion evaluated. This will tell you if your dollar sign expansion is returning a value that matches up with what your invoice date field needs (i.e. a numeric date).

                    Also, you could try hardcoding a date value into your set expression:

                    Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'},
                    Invoice_Date = {40390}>} Sales_Amount)
                    Either of these actions will tell you if it is your Set Analysis or your dollar sign expansion that is the problem.

                    You should also try getting rid of the quotes inside the set modifier of the expression I gave. Since your values are numeric, you don't need the quotes and that could be the problem:

                    Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'},
                    Invoice_Date = {$(=Max(Invoice_Date))}>} Sales_Amount)


                    • Set Analysis Max Date

                      Hi there, you can try adding the function date to the number returned by the max expression, like this:

                      Regards

                       

                      Sum({$<Fin_Prod={'NL'}, Fin_Prod_Description={'OI'},
                      Invoice_Date = {'$(=date(Max(Invoice_Date)))'}>} Sales_Amount)