Set Analysis Based on Another Field (similar to Prior Period but can't use a calculation)

[#1]

Hi everyone,

I'm trying to use set analysis to create a pivot table that shows financial figures for the currently selected period and previous period. I know this question has been asked before, however all of the forum submissions were able to do this via a calculation (e.g. Year - 1). In my application, this isn't possible because the period is a text value and uses business rules to define the previous period. Thus, I have a lookup table which lists all of the periods and what their previous period are (maintained by the user in Excel actually).

For example, I have the following table which contains my data:

Period	Measure	Value
Q2 FY09 Dec OL	Opex	1000
Q2 FY09 Apr OL	Opex	5000

And in another table I have:

Period	PreviousPeriod
Q2 FY09 Apr OL	Q2 FY09 Dec OL

My pivot table needs to look like this:

Measure	Q2 FY09 Apr OL	Q2 FY09 Dec OL
Opex	5000	1000
.....	....	....

The first expression is easy because if someone selects a period then you just do a sum.

The second expression needs to be the previous period but do a lookup of what that is based on another column.

I've tried multiple set analysis expressions, none of which work. Oddly, if I hardcode the value it works.

Here's what I've tried:

sum({$<Period= {$(=only(PreviousPeriod))}>} Value) - didn't work, returned null
sum({$ <Period=PreviousPeriod>} Value) - didn't work, returned 0
sum({$ <Period={PreviousPeriod}>} Value) - didn't work, returned 0
sum({1 <Period=PreviousPeriod>} Value) - didn't work, returned 0

But this works:
sum({$ <Period={"Q2 FY09 Dec OL"}>} Value)

Thanks for the help in advance.

Regards,
Steve

[#2]

the only thing I could come up with is

sum({1<Period={'$(=FieldValue('PreviousPeriod',1))'}>} Value)

But this is static and depends on the load order. I'd recommend you add an additional field to your data so you can define your set and know where up & down is.

[#3]

I'd recommend at least you structure your data as

Data:
load * inline [
Period, Measure, Value
Q2 FY09 Dec OL, Opex, 1000
Q2 FY09 Apr OL, Opex, 5000
];
Periods:
load * inline [
Period, Num
Q2 FY09 Apr OL,1
Q2 FY09 Dec OL,2
];

so you can move around with something like

=sum(FieldValue('Value',Num))

[Anonymous · Accepted solution]

Hi Steve,

As you can see when you hard code the value, you use quotes around the value which is necessary in this case since the text value contains spaces.

If you look at the expressions, for example this:

sum({$<Period= {$(=only(PreviousPeriod))}>} Value) - didn't work, returned null

This will work if the Period didn't have any spaces in it. Instead you have to add quotes around the dollar-sign expansion to get it to resolve in the same manner as the hard coded expression:

sum({$<Period= {"$(=only(PreviousPeriod))"}>} Value)

This should do the trick.

[#5]

Thanks Johannes, that worked! Quick question - is there a way to make this work without using the only() function? Knowing that my lookup contains spaces and w/out creating a key of somesort, can you put double quotes around the field or something to that effect? I tried various solutions but could not get that to work. The reason I'm asking is because I need to keep this expressions as simple as possible since the customer will be maintaining them. Using only() or some other function will add to the maintenance headache.

Thanks!