topic Intersection Multiple fields w/alternative state in QlikView

Intersection Multiple fields w/alternative state

Mon, 18 Mar 2013 15:37:03 GMT

I am tring to get the count of current I_ITM's that have a matching item in the Alternative state PriorPeriod The individual calculations properly isolate the chart row either by family, or by Country but i need it to do both

This works for one or the other

Count({$<Family*=p({PriorPeriod}Family)>}I_ITM)

Count({$<C_CNTRY_SLSCD*=p({PriorPeriod}C_CNTRY_SLSCD)>}I_ITEM)

But I have not figured how to get BOTH in the same ..Count of items where the item was sold in the same country and also in the same item family

Re: Intersection Multiple fields w/alternative state

Gysbert_Wassenaar — Mon, 18 Mar 2013 15:48:27 GMT

For intersection try:

Count({$<Family*=p({PriorPeriod}Family)>*<C_CNTRY_SLSCD*=p({PriorPeriod}C_CNTRY_SLSCD)>}I_ITEM)

Re: Intersection Multiple fields w/alternative state

Tue, 19 Mar 2013 17:03:51 GMT

Thanks Gysbert,

For those who like the color codeing

COUNT({$<Family*=P({PriorPeriod}Family)>*<C_CNTRY_SLSCD*=P({PriorPeriod}C_CNTRY_SLSCD)>}I_ITEM)

the only cavat i would add for people is when using alternative states be sure you are syncing up the right pieces. the Alt State syncronization

using the :: notation to syncronize pieces .. the example above is simplified.

Re: Intersection Multiple fields w/alternative state

Tue, 19 Mar 2013 19:50:53 GMT

This works perfectly for the count of current with match in previous but not in reverse:

Count(

{$<Family*=p({PriorPeriod}Family::Family)> * <C_CNTRY_SLSCD*=p({PriorPeriod}C_CNTRY_SLSCD::C_CNTRY_SLSCD)>}I_ITEM)

Reversing the Equation:

Does not work (gives zeros only) for Count in prior, where match in current:

Count(

{PriorPeriod<Family*=p({$}Family::Family)> * <C_CNTRY_SLSCD*=p({$}C_CNTRY_SLSCD::C_CNTRY_SLSCD)>}
I_ITEM)

Same result for:

Count({PriorPeriod<Family*=p({$}Family)> * <C_CNTRY_SLSCD*=p({$}C_CNTRY_SLSCD)>}
I_ITEM)

I assume its something i am doing wrong obviouly but the editor seems to think they are all ok