Straight table data filtering

[#1]

I am making a P&L table but I only need to show data for the past 3 yrs which means 3 rows of data.

Problem is that the database contains data for more years and for different dates, i.e. there are entries for each quarter.

I need to show the data for 31/12/YYYY for the last three years.

Is it possible to do this in straight table?

[#2]

for example by set analysis

=sum({$<Date={'31/12/2010'}>}value)

=sum({$<Date={'31/12/2011'}>}value)

=sum({$<Date={'31/12/2012'}>}value)

you need to be more specific and write more info about the result you want to achieve

[MayilVahanan]

Hi

Try like this

=Sum({<Date={'$(=AddYears(YearEnd(max(Date)),-3))','$(=AddYears(YearEnd(max(Date)),-2))','$(=AddYears(YearEnd(max(Date)),-1))'}>}Sales)

for all last three years data

=Sum({<Date={">=$(=AddYears(YearEnd(max(Date)),-3))<=$(=AddYears(YearEnd(max(Date)),-1))"}>}Sales)

Hope it helps

[#4]

Where would I put this? Why would I need to use Sum? Sorry for excessive questions but I'm a newbie, any help is appreciated, thanks

I have fields like:

total_revenue, total_costs, tax, date

date field is the one that i want to filter by

[MayilVahanan]

HI

if you want to sum all the total revenue for three year end date, you can use like this

=Sum({<Date={'$(=AddYears(YearEnd(max(Date)),-3))','$(=AddYears(YearEnd(max(Date)),-2))','$(=AddYears(YearEnd(max(Date)),-1))'}>}total_revenue)

for all last three years data

=Sum({<Date={">=$(=AddYears(YearEnd(max(Date)),-3))<=$(=AddYears(YearEnd(max(Date)),-1))"}>}total_revenue)

[#6]

I dont need to do any calculations, I have all the values in the database. All I need to do is only show the values where date is 31/12/YYYY.

Last 3 years would be a bonus. I tried using your statement but it said error in calculation. Of course I replaced the field names with database ones.

I tried using it in a different table and no data is being diplayed, just the table title.

[#7]

All I need is to display it for the past 3 yrs (2011, 2010, 2009 in this case) for 31/12/YYYY (31/12/2011). In this case I would have 31/12/2011, 31/12/2010 and 31/12/2009 left only.

Hope this makes sense and it will be easier to understand what I would like to do

[MayilVahanan]

HI

Try like this

in set analysis

Sum({<Date = {"=Date(floor(YearEnd(Date))) = Date"},Year={'>=$(=Max(Year)-3)<=$(=Max(Year)-1)'}>}total_revenue)

Have a doubt?

=Sum({<Date={'$(=AddYears(YearEnd(max(Date)),-3))','$(=AddYears(YearEnd(max(Date)),-2))','$(=AddYears(YearEnd(max(Date)),-1))'}>}total_revenue)

in this, you can't get the result? can you post a sample?

Hope it helps

[#9]

I do not need to sum anything so thats what i would have in my case:

={<statement_Date={'$(=AddYears(YearEnd(max(statement_Date)),-3))','$(=AddYears(YearEnd(max(statement_Date)),-2))','$(=AddYears(YearEnd(max(statement_Date)),-1))'}>}PROFIT_LOSS_BEFORE_TAX-TAX_RECLAIMED

PROFIT AFTER TAX (PROFIT_LOSS_BEFORE_TAX-TAX_RECLAIMED) is the only expression in the table, others are dimensions.

Date field is called statement_date in the database

How would your set analysis statement have to be adapted to match this?

[#10 · Accepted solution]

So this statement works which means one part of my task is completed:

if(Day(STATEMENT_DATE) = 31 AND Month(STATEMENT_DATE) = 12, date(STATEMENT_DATE))

This only leaves the data values for dates ending in 31/12/YYYY. Now I just need to figure out how to limit it to the last 3 years.

Limited the values by going on Presentation tab and clicking on Max box and choosing 3. Table displays the data that I wanted.

Thanks for help everyone