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

    Whats wrong with this expression?



      I have a dummy table like this:


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



      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.