set expression default issue: maximum revenue month, otherwise GetFieldSelections(MONTH)

[#1]

My goal is to measure the rate from completed revenue versus forcasted revenue. I built a straight table and few year, month listboxs to make this happen. My thought is that if I don't select any of the year or month from the listbox, then I want my expression do the caculation based on the maximum revenue month (September at this point). But if I selected any of the month from this year, I want te caculation based on the selected month instead. I built below variables and expression.

1, vMaxMon=Max({$<DATA_TYPE={'INCRE_REV'}>}MONTH)

2, vSelectedMon=if(isnull(GetFieldSelections(MONTH)),$(vMaxMon),GetFieldSelections(MONTH))

3, vSelectedYear=if(isnull(GetFieldSelections(Year)),$(vCurrectYear),GetFieldSelections(Year))

My expression is below:

=Sum ({$<Year={$(vSelectedYear)},MONTH={$(vSelectedMon)},

DATA_TYPE={'INCRE_REV'}>} AMOUNT)/

Sum({$<Year={$(vSelectedYear)},MONTH={$(vSelectedMon)}, DATA_TYPE ={FORCASTED_REV}>} AMOUNT)

This way worked while I select the month from the listbox. But if I clear everything, my straight table expression would not working anymore. I was hoping that the expression will automatically do the caculation based on the maximun revenue month since this should be the default value.

Does this makes sense? Can anybody let me know why this is not working at somepoint? Any help is appreciated!

[whiteline]

Hi.

It's not clear whether you have used the '=' sign to assign the variables or not.

It seems that there are missing quotes in DATA_TYPE ={'FORCASTED_REV'}

In addition to use this approach MONTH field should contain numeric values.

You can also try to simplify the expressions since Max(MONTH) should always return the maximum possible MONTH.

[#3]

Hi,

Thanks. I used the '=' sign to assign the variables. Oops I missed type the quotes here, but I did not miss the quotes in my expressions. I want to make the max(month) based on the 'INCRE_REV' data type, so I can compare the latest month revenue to the corresponding forecasted revenue.  I tried to use the variable below to make QV set up the default month as the maximum revenue month by the variable below. Do you have any idea that why QV does not sense that? Or is there any better way to make this happen?

vSelectedMon=if(isnull(GetFieldSelections(MONTH)),$(vMaxMon),GetFieldSelections(MONTH))

[whiteline]

I guess the problem is in DATA_TYPE. Could you upload the small sample application ?

[rwunderlich]

Do you want the calculation to use the single selected month or multiple months. If just a single month, can you avoid testing for getFieldSelections() and just always use Max(month)?

-Rob

[#6]

I got a help from someone and the solution solved my issue. The reason my way did not work is because the data type is different from the 'MONTH' list box and the variable vMaxMon. So I changed the variable expresion to below. And it worked! When vMaxMon get return, it showed as '9', but my listbox's number type is 'Sep', that's why. Thanks for trying to help.

(date#(Max({$<DATA_TYPE={'INCRE_REV'}>}MONTH)&'\01'&'\2014','MM\DD\YYYY'))

[#7]

Hi Rob,

I want the calculation to use the single selected month. I need to consider this in the next step. I just replied whiteline about the solution I got from someone. Thanks for trying to help!

(

I got a help from someone and the solution solved my issue. The reason my way did not work is because the data type is different from the 'MONTH' list box and the variable vMaxMon. So I changed the variable expresion to below. And it worked! When vMaxMon get return, it showed as '9', but my listbox's number type is 'Sep', that's why. Thanks for trying to help.

(date#(Max({$<DATA_TYPE={'INCRE_REV'}>}MONTH)&'\01'&'\2014','MM\DD\YYYY'))

)