Dynamic Set Analysis From Inline Table Values

[#1]

Hi,

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:

Periods_Menu:
NoConcatenate
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?

Thanks!

[swuehl]

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.

[#3]

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?

Thanks,

Yaniv

[jonathandienst]

Hi

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.

HTH

Jonathan

[stabben23]

Hi,

You can add a field in your inline like this:

Periods_Menu:

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;