Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
felipe_oliveira
Contributor III
Contributor III

Max Year/Month available lower than selected filter period

Hi Masters

My date filter is:

Qlik_filters.PNG


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

1 Solution

Accepted Solutions
felipe_oliveira
Contributor III
Contributor III
Author

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

View solution in original post

13 Replies
zebhashmi
Specialist
Specialist

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
Contributor III
Contributor III
Author

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:

Error.PNG

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
Contributor III
Contributor III
Author

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

zebhashmi
Specialist
Specialist

yap you are right!

Digvijay_Singh

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

felipe_oliveira
Contributor III
Contributor III
Author

I get the same error above

Error.PNG

zebhashmi
Specialist
Specialist

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
Contributor III
Contributor III
Author

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