3 Replies Latest reply: Feb 25, 2014 10:00 AM by Sunil Chauhan RSS

    calculated dimension

      why cannot i do this in a calculated dimension?

       

      =if(sum({<DateCleanLog={"=$(=Date(Today()-1))"}>} Rows) = 0, null(), LogCustomerID)

       

      so if there was no rows yesterday, then dont show logcustomerid, else show customerid  - why cnat i do so?

        • Re: calculated dimension
          Nicole Smith

          Sum is an aggregation expression, and in order to use it in a dimension, it must be used within aggr(), so something like the following may work for you:

          =aggr(if(sum({<DateCleanLog={"=$(=Date(Today()-1))"}>} Rows)= 0, null(), LogCustomerID), LogCustomerID)

          • Re: calculated dimension
            Alessandro Saccone

            Because when you perform the sum, the same sum is computed according specified dimensions so if you need something like the one you have written you must use Aggr function

             

            Hope it helps

            • Re: calculated dimension
              Sunil Chauhan

              beacuse aggregate function we can use directly in dimension

               

              it will show you "Error in dimension"

               

              so for your expression to work you need to use aggr keyword available in qlikview

               

              if(aggr(sum({<DateCleanLog={"=$(=Date(Today()-1))"}>} Rows),LogCustomerID) = 0 ,null(), LogCustomerID)




              then it whould work


              basically aggr function used to group by some field  as is in ur example i have grouped your  expression of sum by


              LogCustomerID


              hope this helps