topic Set Analysis using several fields in QlikView

Set Analysis using several fields

picturebox — Wed, 04 Nov 2009 19:32:06 GMT

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

Set Analysis using several fields

Wed, 04 Nov 2009 19:40:40 GMT

Hi,

Try this:

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

Kind regards,

Janusz

Set Analysis using several fields

picturebox — Wed, 04 Nov 2009 20:02:14 GMT

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

Set Analysis using several fields

picturebox — Wed, 04 Nov 2009 22:15:29 GMT

Hi,

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

Appreciate any suggestions...

Thanks,

Petra

Set Analysis using several fields

Oleg_Troyansky — Wed, 04 Nov 2009 23:32:35 GMT

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

Set Analysis using several fields

picturebox — Thu, 05 Nov 2009 17:04:26 GMT

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

Set Analysis using several fields

picturebox — Thu, 05 Nov 2009 17:39:18 GMT

okay got it! Looks like this:

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

Thanks for your help!