3 Replies Latest reply: Mar 19, 2013 3:50 PM by Nick Dighera RSS

    Intersection Multiple fields w/alternative state

    Nick Dighera

      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

          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
              Nick Dighera

              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
              Nick Dighera

              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