How to use a field not linked to anything in an expression

[#1]

Hi,

I've worked a while on a problem but I really can't find a way to solve it.

I need to show in a table two kind of data: collection and forecasts for credits, where both collection and forecast has amount and data.

I created two list box, one with field Year and one with field Month (this fields are not linked to anything).

What I need is that when Year and Months aren't selected in the table I see the total revenues and the total forecasts. When year is selected I want to see only the revenues and the forecasts for that year, when also month is selected I want to see only revenues and forecast for that month and year.

When I had only the check on the year it worked with sum(if(year(Revenue_day)=Year,Amount,0). Adding the check on the month I'm not able to find a solution.

Do you have any idea how to do it?

Thanks,
Marco

[brenner_martina]

Hi,

maybe you can use the function GetSelectedCount(Year)=1 resp. Month

If(GetSelectedCount(Month)=1,
sum(if(year(Revenue_day)=Year And Num(Month(Revenue_day))=Num(Month),Amount,0),
sum(if(year(Revenue_day)=Year,Amount,0))

[#3]

I tried but it doesn't work.

It calculates a value of revenues smaller than the actual and moreover when I select year and month it gives me 0

[#4]

And if i try

=sum(if(GetSelectedCount(month)=1,if(year(Rev day)=year and month(Rev day)=month,Amount,0),Amount))

it gives me a much bigger value than the actual

[#5]

Why not connect date and month, year?

[#6]

Or make fields in load script Revenue_month = month(Revenue_day) and Revenue_year = year(Revenue_day)

and expression like this

sum({$<Revenue_year  = Year, Revenue_month = Month>}Amount)

or if you want to isolate this expression from other filters

sum({1<Revenue_year  = P({$} Year), Revenue_month = P({$} Month)>}Amount)