Inputbox for equal, less and greater than and Set Analysis

[#1]

Hi all!

Please help me.

I have Sales and I want to get sales where sales >,<,=N

I use 2 inputboxes for  input “>,<,=” and for input TestNum =“N”.

I use expression =If(sum(Sales)>TestNum, sum(Sales)) and If(sum(Sales)<TestNum. It works.

I wanna use set analysis, but it works only witth 'If'.  See attachment.

[Anonymous]

I don't think you can put an aggregating expression on the left of a comparison in set analysis, such as you do with the vSales variable which equals sum(Sales). Instead a field is expected.

Jonathan

[#3 · Accepted solution]

Hi,

Try this set analysis.

For '>'

=sum( {<Category={"=sum(Sales)>$(TestNum)"}>}Sales)

For '<'

=sum( {<Category={"=sum(Sales)<$(TestNum)"}>}Sales)

Sean

[erichshiino]

Sean's example can be adjusted to be used without if:

=sum( {<Category={"=sum(Sales)$(Test)$(TestNum)"}>}Sales)

The expression will use < or > in the variable Test directly

Hope it helps

Erich

[#5]

Thank you!

[#6]

Hi Erich,

I am trying to automate the greater than or less than symbol as you suggested .But its not working.

Expression:

if(aggr(($(RevenueC)+$(Forecast)),[Parent Customer])$(Vsymbol)$(AcctSizeDL),$(RevenueC)+$(Forecast))

AcctSizeDL

Pick(Match(vAcctSize,'Accounts With>5M Revenue','Accounts With > 1M Revenue','Accounts With < 1M Revenue','All Accounts'),'5000000','1,000,000','1,000,000')

Vsymbol

Pick(WildMatch(vAcctSize,'*>*','*<*'),'>=','<','>=')

Please let me know what mistake i have done