Filter question

[#1]

Hi All,

I created an app and is working fine. In the app, I want to add a table chart that shows sales this year in one column and sales prior year in second column.  But here is the problem- I have a list box with all the years and if I select a particular year as filter, it gets applied to this chart as well screwing it up. Any idea how to deal with htis situation?

Thanks in advance!

[lironbaram]

in the expression in the chart use set analysis

something like this

sum({<YEAR={">=$(=max(YEAR)-1)"}>} Sales)

hope its helps you

[#3]

The values change when I click on a particular month. Since this is a YTD calculation, I don't want it to change.

Question - HOw do you guys deal with this situation. Let's say you have a several charts in the report and one table chart that shows sales ytd current yera and sales ytd previous year in two separate columns. Should we have the values not change when user clicks on a year/month or should we have the values in this chart static?

Please advice.

[#4]

one possibility is to create a new table calendar with this columns

Actual year , month, Previous Year, Mark actual, Date (this is the field to be linked with the fact table)

2011                 01             2010               S         2011-01

2011                 01             2010               N         2010-01

2011                 02             2010               S         2011-02

2011                 02             2010               N         2010-02

The user have to select the field Actual Year and Actual Month

In  the chart it is very important the usage of the MARK ACTUAL by using IFs.

In case you need diferent combination of dates you can create more field and more marks.

I hope you could sove your problem.

Besides this table is very small and the performance of the application is not going to be worst.

Regards,

Marcelo

[#5]

Is there a way to modify the folrmula SUM({<YEAR={">=$(=max(YEAR)-1)"}>} Sales) so that it doesn't affect when users click on a month?

[SunilChauhan]

use this formula

SUM({1<YEAR={">=$(=max(YEAR)-1)"}>} Sales)

[#7]

I don't understand well what happens in your case when user clicks on a month, but usually this approach (as below) helps to ignore selection in a certain field (here - Month).

SUM({< Month=, YEAR={">=$(=max(YEAR)-1)"}>} Sales)

UPDATE

Sunil Chauhan correctly suggests to use {1} for calculation of a previous period, not included into current selection. So final formula should be

SUM({1 < Month=, YEAR={">=$(=max(YEAR)-1)"}>} Sales)

[#8]

you'll need to also put your Month field in that set analysis or it will still be effect it

SUM({1<YEAR={">=$(=max(YEAR)-1)"},[your month field]= >} Sales)

so now it will limit to your year and will also ignore your month selections

hope that helps

Joe

[SunilChauhan]

SUM({1<YEAR={">=$(=max(YEAR)-1)"}>} Sales)

no otheer extra code is required

[#10]

oh that's interesting so the '1' there is basically a catch all for disregarding everything else apart from the Year field? good to know cheers

Joe