2 Replies Latest reply: Jun 24, 2016 2:28 AM by Michael King RSS

    Greater Than Expression Issue

    Michael King

      Hi All,

       

      I am trying to find the total number of transactions over the course of a promotion lifetime. Currently I have the below:

       

      'BW - ' & Count({<[Location]={'LocationA'}, [Date]={">=$([Promotion Start Date])<=$([Promotion End Date])"}>} DISTINCT [Sales invoice])

       

      In tables, promotion dates are linked to promotion names. These in turn are linked to promotion products.

       

      i.e.

      PROMOTIONS

      PromotionPromotion Start DatePromotion End Date
      PROMOTION-101/01/201631/01/2016
      PROMOTION-201/02/201631/03/2016

       

      PROMOTION PRODUCTS

      PromotionProduct Code
      PROMOTION-1PRODUCT-A
      PROMOTION-1PRODUCT-B
      PROMOTION-2PRODUCT-A
      PROMOTION-2PRODUCT-C

       

      The hope is, when I filter to, say PROMOTION-1 via filter pane, the value (KPI) will update to show the total count of [Sales invoice] over that promotion period (01/01/2016 - 31/01/2016). However, it is pulling the total number of transactions in the period ONLY where a promotion product is included.

       

      Where I would hope to see 2000 transactions, I am instead seeing 150 etc.

       

      Any assistance would be much appreciated.

       

      Regards,

       

      Michael

        • Re: Greater Than Expression Issue
          Stefan Wühl

          Try clearing your selection in Promotion in the set expression (which will filter the product codes and thus the invoices, either by clearing only the field or using set identifier 1 (which will ignore all other selections as well. Not that the dollar sign expansion in the search expression is still considering your selection (though I think it should be written differently):

           

          Count({<[Location]={'LocationA'}, [Date]={">=$(=Min([Promotion Start Date]))<=$(=Max([Promotion End Date]))"}, Promotion= >} DISTINCT [Sales invoice])

           

          or

           

          Count({1<[Location]={'LocationA'}, [Date]={">=$(=Min([Promotion Start Date]))<=$(=Max([Promotion End Date]))"}>} DISTINCT [Sales invoice])