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

[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)

    )

- The overall syntax looks good, can you share variables definition, it may need some change based on how it is defined.

[felipe_oliveira]

The error message disappeared but I get only zero now.

vMaxDate = date(peek('Max_Date_DWH'))

vMaxYear = year(peek('Max_Date_DWH'))

vMaxMonth = month(peek('Max_Date_DWH'))

vFieldYear = max([Year])

vFieldMonthName = month(makedate($(vFieldYear),$(vFieldMonth)))

[Digvijay_Singh]

One more try

=if(Year>=$(vMaxYear) and Month>$(vMaxMonth),

     sum({1<Year={"=$(=vMaxYear)"}, Month={'$(vMaxMonth)'}>} NET_BAL),

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

    )

[felipe_oliveira · Accepted solution]

Thanks Digvijay!

At the end I made some changes, for example, the months variables are now showing the month number instead of month name.

vMaxMonth = 2

vFieldMonth = 2

After making those changes the query below worked as expected:

if($(vFieldYear)>=$(vMaxYearDWH) and $(=($(vFieldMonth#)))>$(vMaxMonth#DWH),

    num(sum({1}{<Year={$(vMaxYearDWH)}, Month={$(vMaxMonthDWH)}>} NET_BAL),'$#,##0;($#,##0)'),

     num(sum({1}{<Year={$(=($(vFieldYear)))}, Month={$(=($(vFieldMonthName)))}>} NET_BAL),'$#,##0;($#,##0)')