4 Replies Latest reply: Apr 12, 2014 6:27 PM by Staffan Johansson RSS

    Dynamic Set Analysis From Inline Table Values


      I am trying to create a straight table that will show me a bunch of calculations where each row represents a different time period (as seen in the attached file), so the only difference is the Date dimension.

      For this, I have created the following inline table:

      LOAD * INLINE [
      %Period_Desc, %Period_Date_Diff, %Period_Sort_Val
      Yesterday, -1, 1
      Last 7 Days, -7, 2
      Last 30 Days, -30, 3
      Last 90 Days, -90, 4


      I have a field in my calendar called 'Date Diff' which represents the difference in days from today, so the date of a week ago will have the value of -7, the date of yesterday will have the value of -1, today will have the value of 0 etc.

      What I am trying to do is set the 'Date Diff' value to be greater than the value that is shown under the %Period_Date_Diff field in my inline table, but without success.

      I tried using the only() function but that happens to work only if one period is selected. I also tried using the 'Pick' and 'Match' functions, but still without any success.

      Here is the expression I tried using:

      pick(Match(%Period_Desc, $(=chr(39)&concat(%Period_Desc, chr(39)&','&chr(39),%Period_Sort_Val)&chr(39))),

      $(=Concat(%Period_Date_Diff, ',',%Period_Sort_Val)))

      this does give me the right value of %Period_Date_Diff for each row in the table, but doesn't work when trying to put this as a set modifier.


      Can anyone help me out with this?



        • Re: Dynamic Set Analysis From Inline Table Values
          Stefan Wühl

          A set expression is only evaluated once per chart, not per dimension value, so you can't create a set expression that is responsive to your row's dimension value.


          But as you already approached to, you can use a pick / match combination, but you will need to enter several expressions with set analysis into the branches, something like (simplified):


          =pick ( match( %Period_Desc, 'Yesterday', 'Last 7 Days')

          ,sum({<[Date Diff] = {">=-1"}>} Value)

          ,sum({<[Date Diff] = {">=-7"}>} Value)



          You can try to create this expression as variable in your script, using your table as input, but that's another story.

            • Re: Dynamic Set Analysis From Inline Table Values

              Thanks a lot for the reply!

              I see what you are saying, the problem is I have quite a lot expressions to manage, so I figured it'll be easier to handle it using this set analysis option. But, according to your reply, I guess this isn't possible.

              Do you have any recommended way of applying this?




                • Re: Dynamic Set Analysis From Inline Table Values
                  Jonathan Dienst



                  If you are using a master calendar, you can assign a set of flag fields in the calendar to indicate the date perdiods of interest to you. Something like this in the master calendar load:


                       LOAD date As Date,

                            month(date) As Month,


                            If(date = Today() - 1, 1, 0) As IsYesterday,

                            If(date > Today() - 7, 1, 0) As IsLast7Days,

                            If(date > Today() - 90, 1, 0) As IsLast90Days,



                  Now you can use the flag fields in a sum(if()) or set expression to select the dates in the period of interest.




              • Re: Dynamic Set Analysis From Inline Table Values
                Staffan Johansson


                You can add a field in your inline like this:


                LOAD * INLINE [

                %Period_Desc, %Period_Date_Diff_Start, %Period_Date_Diff_End

                Yesterday, -1,  -1

                Last 7 Days, -7, -2

                Last 30 Days, -30, -8

                Last 90 Days, -90, -31 ];

                you can now do a intervallmatch on you "DateDiff" field and get all diff values to match your %Period_Desc

                left Join IntervalMatch(DateDiff) LOAD %Period_Date_Diff_Start, %Period_Date_Diff_End RESIDENT Periods_Menu;