Set Analysis using several fields

[picturebox]

Hello Community,

I am trying to use set analysis over several fields, but am having a problem with the syntax. Maybe someone can help?

Here is my problem: This set

=

num(SUM( {$ <CW ={'$(vMaxCW)'}, SalesQuarter= , Salesmonth=, SalesWeek= >} UnitSalesIncl), '#.##0 kr')

fixes the Calendar Week CW to the value of vMaxCW, no matter what other calendar entries are chosen. Works great. Then I also have another set which sums over the same time period as above, but only for a selected contract id SelContractID. Here I am using an if statement for the date control which is not good as whenever you select another date, this invalidates ... so here is the original:

=

num(sum({$< ContractID = SelContractID >} if (InWeek(SalesWeek, MaxSalesWeek,0), UnitSalesIncl, 0)),'#.##0 kr')

As you can imagine, I want to combine the 2 sets and get rid of the IF. I cannot seem to get the syntax to work though.

Can someone help me please?

Thanks and best regards,

Petra

[#2]

Hi,

Try this:

num(SUM( {$ <CW ={"$(=$(vMaxCW))"}, SalesQuarter= , Salesmonth=, SalesWeek= >} UnitSalesIncl), '#.##0 kr')

Kind regards,

Janusz

[picturebox]

Thanks,

that returns zero, whereas the if statement returns a value. The SelContractID is a totally non-connected table whereas the ContractID is connected to the data ...

I cannot see in your set where you reference the ContractID / SelContractID except with the $ for selected data ...

Regards,

Petra

[picturebox]

Hi,

additional Info: I am using 8.5 not 9.0 ....

Appreciate any suggestions...

Thanks,

Petra

[Oleg_Troyansky]

what happens if you add your addition condition for the selected contract, into the same Set Analysis expression? I should work just fine...

[picturebox]

Hi,

this is where I am having the problem.... how do I set the different brackets so it accepts both sets? LIke the below it only sees the second set and ignores the first set ...

=

num(SUM( {$< ContractID = SelContractID >} {$<CW ={'$(vMaxCW)'}, SalesQuarter= , Salesmonth=, SalesWeek= >} UnitSalesIncl), '#.##0 kr'

)

Thanks,

Petra

[picturebox · Accepted solution]

okay got it! Looks like this:

=num(SUM( {$< ContractID = SelContractID , CW ={'$(vMaxCW)'}, SalesQuarter= , Salesmonth=, SalesWeek= >} UnitSalesIncl), '#.##0 kr')

Thanks for your help!