Input Variables to calcluate column value

hstrode — Fri, 15 Nov 2019 19:20:09 GMT

Hello! I'm currently creating a goals app in which i have several input variables. On one sheet, I have each of the sales territories with an input variable that is typed in by user. That's 21 input variables that I'd like to put into one column based on their selection. I can get one variable to display based on

IF(IF(CombinedAgencyFieldAreaNo=$(Field Area), $(Field Area Growth %), column(5))>0, IF(CombinedAgencyFieldAreaNo=$(Field Area), $(Field Area Growth %), column(5)), .02)

but when i try to add $(Field Area 2) and $(Field Area 2 Growth %), it errors or displays $0.

Any help would be greatly appreciated!

Re: Input Variables to calcluate column value

hstrode — Sat, 16 Nov 2019 15:05:25 GMT

For anyone who needs this in the future, I solved it with multiple trials and errors. Overall, the base formula looks like

SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 1)'}, StateAlphaCd = {'$(State)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 1)')

For each of the 10 input field sets, I just added them all together. So, a little somethikng like:

(SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 1)'}, StateAlphaCd = {'$(State)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 1)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product2)'}, StateAlphaCd = {'$(State2)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 2)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 3)'}, StateAlphaCd = {'$(State 3)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 3)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 4)'}, StateAlphaCd = {'$(State 4)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 4)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 5)'}, StateAlphaCd = {'$(State 5)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 5)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 6)'}, StateAlphaCd = {'$(State 6)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 6)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 7)'}, StateAlphaCd = {'$(State 7)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 7)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 8)'}, StateAlphaCd = {'$(State 8)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 8)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 9)'}, StateAlphaCd = {'$(State 9)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 9)'))
+ (SUM({<AcctDtYearToDateFilter ={'YTD'}, AgencyProductNam = {'$(Product 10)'}, StateAlphaCd = {'$(State 10)'}>} NewBusinessPremiumAmt)*('$(Premium Adjustment 10)'))

topic Input Variables to calcluate column value in App Development

Input Variables to calcluate column value

Re: Input Variables to calcluate column value