8 Replies Latest reply: Mar 15, 2013 6:42 AM by christian juillard RSS

    Set analysis syntax

    christian juillard

      Hi everybody,

       

      as far as i remember i used to script set analysis like this

       

      sum({<DateJ={$(=max(DateJ)) }>}  Nb)

       

      when i write

      sum({<DateJ={'31/01/2013'}>}  Nb)  it works, nevertheless i want to do so for everymonth in a pivot table so max(DateJ) should be given by month

       

      Regards

      Chris

        • Re: Set analysis syntax
          Gysbert Wassenaar

          The syntax is correct, but you have to understand that a set analysis expression creates only one set for the entire chart. The dimensions are then applied to the set. The set is not calculated per row. So if you use month as a dimension you'll see one month with a value. The month with the overall max date. You could try using a calculated dimension =dual(Month,aggr(max(DateJ),Month)). Change Month to the name of your month field.

            • Re: Set analysis syntax
              christian juillard

              Thank you Gisbert  you're right i forgot that point but anyway even for 1 month it doesn't work.

              I tried IF as well but doesn't give much.

              What i want is the value of the last day for every month. Any idea?

               

              Chris

                • Re: Set analysis syntax

                  If you have a master calendare, you can load a new field, say EndOfMonth, that will contain 1 of the day is the end of the month, 0 if not. Then you can sum({<EndOfMonth=1 }>} Nb) as you expression.

                    • Re: Set analysis syntax
                      christian juillard

                      Thank you, it could be the way to do so but actually it's a bit more complicated because i'm using a cyclic group with day, week, month, year dimension.

                      When it is day i want sum(Nb) but for others I just want the sum({<Date={max(date)}>} Nb)

                       

                      regards

                      Chris

                        • Re: Set analysis syntax

                          Since you have a cycle widget, you can load the last day of the week, month, year, and day (it will make the expression easier) into separate fields in the master calendar. You will need name the fields with the field names used in the cycle widget, e.g., LastDayOfDay, LastDayOfWeek, LastDayOfMonth, LastDayOfYear if your dimensions are Day, Week, Month, Year.

                           

                          For your expression you will need to use the GetCurrentField to get the select dimension and use something like sum({<LastDayOf$(=GetCurrentField(DateSelection))=1>} value).

                            • Re: Set analysis syntax
                              christian juillard

                              Hi

                              it was a good idea but i can't use it because the value i need is not endofyear/month/week but max(year/month/week) according to the current selection.

                              I don't need a full week/month/year to be finished to display the result. It can be in the middle of it.

                               

                              I just don't understant why sum({<max(DateFromTable2) = { $(=max(DateSelectedTable1)) } >} Nb ) doesn't work.

                              Neither sum(if (max(D1) = max(D2), Nb) )

                               

                              regards

                              Chris