Max Year/Month available lower than selected filter period

[felipe_oliveira]

Hi Masters

My date filter is:

Variables:

vMaxDate = 01-Feb-2018    <==Max available date

vMaxYear = 2018                 <==Max available Year

vMaxMonth = Feb                <==Max available date

vFieldYear = 2018                <==Max selected year (filter)

vFieldMonthName = Dec     <==Max selected Month (filter)

Since we are now in February 2018, the maximum year and month available in my database is "2018" and "February".

I would like to write a script in a text box that would work like the following:

=if(Year>=$(vMaxYear) and Month>$(vMaxMonth),sum({1}{<Year={$(=($(vMaxYear)))}, Month={$(=($(vMaxMonth)))}>} NET_BAL),sum({1}{<Year={$(=($(vFieldYear)))}, Month={$(=($(vFieldMonthName)))}>} NET_BAL))

Basically, if the selected year and month (from the filter - 2018/Dec) are higher then the maximum year and month available (which is 2018 and Feb) than the disclosed result should be the max year/month available otherwise it should disclose the results for the selected period (filter).

Please advise

Thanks

Felipe

[zebhashmi]

I did not get well what you are looking for,

Let's try by fixing some syntax are you using {1} veriable? if not we can continue..

=if(Year>=$(vMaxYear) and Month>$(vMaxMonth),sum({<Year={$(vMaxYear)},Month={$(vMaxMonth)}>} NET_BAL),

,sum({<Year={$(vFieldYear)}, Month={$(vFieldMonthName)}>} NET_BAL))

[felipe_oliveira]

Thanks Jahanzeb

I used this script:

=if(Year>=$(vMaxYear) and Month>$(vMaxMonth),sum({<Year={$(vMaxYear)},Month={$(vMaxMonth)}>} NET_BAL),sum({<Year={$(vFieldYear)}, Month={$(vFieldMonthName)}>} NET_BAL))

but I am getting the error below:

[Digvijay_Singh]

May be this -

=if(Year>=$(vMaxYear) and Month>$(vMaxMonth),sum({1<Year={$(vMaxYear)}, Month={$(vMaxMonth)}>} NET_BAL),sum({<Year={$(vFieldYear)}, Month={$(vFieldMonthName)}>} NET_BAL)

[felipe_oliveira]

it is missing a ")" at the end. You need to close the "if" and "sum" statements

[zebhashmi]

yap you are right!

[Digvijay_Singh]

My bad, after ')' is it giving the required output?

[felipe_oliveira]

I get the same error above

[zebhashmi]

try this one , was extra

=if(Year>=$(vMaxYear) and Month>$(vMaxMonth),sum({<Year={$(vMaxYear)},Month={$(vMaxMonth)}>} NET_BAL)

,sum({<Year={$(vFieldYear)}, Month={$(vFieldMonthName)}>} NET_BAL))

[felipe_oliveira]

This is the script I posted on my first reply to you. It didn't work.