8 Replies Latest reply: Aug 21, 2012 8:01 AM by Meelis Karulin RSS

    Whats wrong with this expression?

      Hello.

       

      I have a dummy table like this:

       

      ProductCodeBeginDateEndDate
      Product 14010040110
      Product 24012040130
      Product 34014040150
      Product 14016040170
      Product 24017040180
      Product 34019040200



       

       

      ProductCodeTransDateTransId
      Product 140101random #
      Product 140161random #
      Product 240121random #
      Product 240171random #
      Product 340141random #
      Product 340191random #

       

       

      I am trying to use a straight table to count the transaction ID-s (TransId) of products in the last campaign by comparing the sales date (TransDate) with the highest BeginDate to the highest EndDate (Only the rows marked as bold are the ones i want to count)

       

       

      ProductCodeTransactions alst campaignLast campaign period

      Product 1

      140160 - 40170
      Product 2140170-40180
      Product 3140190-40200

       

       

       

      Min() and max() dont work inside if() condition so i used aggr.

       

      =count(distinct if(TransDate>=aggr(max(BeginDate),ProductCode) AND TransDate<=aggr(max(EndDate),ProductCode),TransId))
      

       

      But it does not work, i get 0 or 1, when there should be 10 or 100. (in real data)

       

      When i use =aggr(max(BeginDate),ProductCode) in the next column or replace the aggr() in the formula with date numbers i get the correct result.

       

      What's wrong with my code?

       

      I also added dummy sales table where are the IDs i want to count and how the result table should look like.