2 Replies Latest reply: Mar 11, 2014 1:02 AM by jay chen RSS

    How to use Aggr NODISTINCT in ad-hoc report

      Hi all,

       

      Example I have a data like this.

       

      LOAD * INLINE [

      Network,    Client,    Demo,        Cost,         IMP

      ABC,        C1,        AD7-15,        0,             10000

      ABC,        C1,        AD15-25,      0,             55000

      ABC,        C1,        AD25-35,      0,             20000

      ABC,        C1,        AD35-45,      0,             20000   

      ABC,        C1,        AD45-55,      0,             12000

      ABC,        C1,        All Demo,     700000,    117000

      ABC,        C2,        AD7-15,        0,             10000

      ABC,        C2,        AD15-25,    0,               55000

      ABC,        C2,        AD25-35,    0,               20000

      ABC,        C2,        AD35-45,    0,               20000   

      ABC,        C2,        AD45-55,    0,               12000

      ABC,        C2,        All Demo,    500000,     117000

      ];

       

      Because we don't have exactly cost amount for each demo so in each demo of client we want to show the value of 'All Demo' Value. I create an adhoc report for user, when user select client dimension, I want the output like this:

       

      Network            Client               Demo                                             Cost                   IMP

      ABCC1AD15-2570000055000
      ABCC1AD25-3550000020000
      ABCC1AD35-4550000020000
      ABCC1AD45-5550000012000
      ABCC1AD7-1550000010000
      ABCC2AD15-2550000055000
      ABCC2AD25-3550000020000
      ABCC2AD35-4550000020000
      ABCC2AD45-5550000012000
      ABCC2AD7-1550000010000

      or dis-select, I want the out put like below:

       

      ABCAD15-251000000110000
      ABCAD25-35100000040000
      ABCAD35-45100000040000
      ABCAD45-55100000024000
      ABCAD7-15100000020000

      I can handle this with an If condition,

       

      If Client dimension is checked then:

      Aggr(NODISTINCT Sum({<[Demo]={'All Demo'}>} Cost), Network, Client)

      else

      Aggr(NODISTINCT Sum({<[Demo]={'All Demo'}>} Cost), Network)

       

      But in reality, there are more than 10 dimensions for user to select not only client and network, I cannot cover the selected dimensions in Aggr function. Do we have any idea to solve this?

       

      Please refer to attached file for more detail