Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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))
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.
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!