Selected value of a list box YTD comparison

[#1]

A YTD Comparison question.. Currently I'm loading our fiscal dates and fiscal year into a table. The fiscal year is located in a list box that allows the end user to select the fiscal year they wish to view. The pivot table has a 2008 column and 2009 column respectively. However, this is a static method where I load code into a 2008 field if it is a specific date range.

What I want to do is make it dynamic. If a user uses the list box and selects fiscal year 2009 I want to automatically know that the previous year is [Current year selected in list box] - 1.

Not sure how to go about this or pulling the (current selected value) from a list box.

Any thoughts or pointers in the right direction would be much appreciated.

Thanks,

Anthony

[sna]

I think you are looking for a function called, GetFieldSelections. So the syntax is GetFieldSelections (FiscalYear) which returns 2009, so -1 of that is 2008 dynamically.

But probably the best way is to use set analysis if you are using 8.5+.

Max function:

For Current Year = Sum({$<Year={$(=Max(Year))}>} Sales)

For Last Year = Sum({$<Year={$(=Max(Year)-1)}>} Sales)

GetFieldSelections:

For Current Year = Sum({$<Year={$(=GetFieldSelections(Year))}>} Sales)

For Last Year = Sum({$<Year={$(=GetFieldSelections(Year)-1)}>} Sales)

I hope this helps.
Shima

[#3]

This helps a lot and I appreciate your reply. In regards to set analysis I've tried that. One thing I notice is it pretty much breaks the "show partial sums" total rows when used.

Do you know why this is or if there is a possible method around it?

Thanks for your input.

Anthony

[blaise]

Have a look at the RowNo() function. You can, with the RowNo() have a different expression for your partial sums. I dont know for sure (havn't axx to Qv atm) but i think it is something like this;

if(RowNo()=0,<expression for partial sum>,<ordinary expression>)

[#5]

Here's another idea.

If you have a date field in the data (I'm assuming you have a true date field or a year field), then you can avoid set analysis. I'll call that field Year. I'll also call Cost the field you want t calculate.

Here's what you do -

Create a variable in an input box called Year_Var. Year_Var has to have Enable Edit Expressions turned off, set to values in a predefined list, force a list of values 2009;2008;2007;2006 etc... and set your default value to 2009.

Next, create a chart and set the enable condition on the table to tell the customer to select at least two distinct years (e.g you can do this with show condition of count(distinct Year) > 1. You can set up a calculation message as well with this.

In the chart, select a straight table and pivot table. Select the dimensions you want. Don't add in dates to the dimensions.

In the expressions tab, add three expressions:

Expression 1 => sum

(if(Year_Var=Year,Cost,0)) For the title => =Year_Var

Expression 2 => sum(if(Year_Var-1=Year,Cost,0)) For the title => =Year_Var -1

Expression 3 => sum(if(Year_Var=Year,Cost,0)) - sum(if(Year_Var-1=Year,Cost,0)) For the title => Delta

This should be all you need to do. Hopefully this will do everything you want while and will probably be more efficient than set analysis. It also gives you more flexibility to create year-to-date analysis utilizing if-then logic.

[rwunderlich · Accepted solution]

One approach is to copy the Year values into a disconnected "date island" field.

Years:
LOAD Year as Selected_Year RESIDENT data;

Put Selected_Year in a listbox with "always one selected".

For your charts, use a calculated dimension like:
=if(match(Selected_Year - Year, 0, 1), Year)

Eample attached.

-Rob

[#7]

Rob, I like your reply. However, I'm trying to implement this in the column area where the dimensions do not have the date but the expression is based off the selected date and the previous year.

Thoughts?

[#8]

I think that if you want to force the user to have just one year selected and you want to compare it to a previous year of data, then you need to use set analysis.

If you don't care if they have multiple years selected, then you can use Rob's suggestion of a year with a list based off of your load data (which I happen to like as it's dynamic each time you reload) or utilizing a variable with fixed parameters.

[rwunderlich]

You can still use this as an expression even if Year is not a dimension in the chart. Like if you were trying to sum the sales for the selected year and it's prior year.

=Sum(if(match(Selected_Year - Year, 0, 1), Sales))

or Sales for the just the prior year.

=Sum(if(Selected_Year - Year = 1, Sales))

-Rob

[#10]

Rob, Thanks again for your help/replies and to everyone else for coming up with such uniqe ideas.

I'm currently trying to implement your last suggestion and I can't get the previous year/current year to display at the same time. Here is my screen shot and code. Any input would be appreciated.

And looking at the code i can agree that it wont show previous and current at the same time depending on year selected. But have been unsucessful when trying to find an alternative.

Thanks,

if

(match(Selected_year - Year, 1), sum([Invoiced]

))

if

(match(Selected_year - Year, 0), sum([Invoiced]))