Data issue in back end aggregation

[saradhi_it]

Hi

we are planning a  search dashboard with the data was huge we did some front aggregations like (count(distinct) in the dashboard but for optimization purpose my manger told me to all the aggregations ,if i did back end aggregations my data was getting doubled

Example : Count(DISTINCT if(cdate>= vStartdate and cdate<=vEnddate and stage_no=1, cookie))  the value was 63130

with aggregationed changes in the backend  i used this formula

               sum(DISTINCT if(cdate>= vStartdate and cdate<=vEnddate and stage_no=1, Search) the value was 124212

it was doubled and it was wrong , i tried so many changes i commented the stages and cookie columns but the result was same

i'm sharing the code which i used

SEARCH:

LOAD

autonumber(cookie & cdate) as cookie,

     date(cdate) as  cdate,

     MonthName(cdate) as c_Month,

    channel,

   stage,

   stage_no,

     Booking_no,

     test_ids

FROM

(qvd)

where   match(stage_no,'1','5','7','8','9','10')

and cookie <>'Unknown' and match(channel,'SITE','MOBILE');

  NoConcatenate

SEARCH1:

LOAD 

    cookie,

    cdate, 

     channel,

     stage,

   stage_no,

    Booking_no,

     test_ids

      count(DISTINCT if( stage='1', cookie)) as STAGE1,

     Count( DISTINCT if( stage=8, cookie)) as STAGE2,

    Count( DISTINCT if( stage=9, cookie)) as STAGE3,

     Count( DISTINCT if(stage=10, cookie)) as STAGE4,

     Count( DISTINCT if( stage=7, cookie)) as STAGE5,

Resident SEARCH

where  match(channel,'SITE') AND len(test_ids)>0

group by

apache_cookie,

  cookie,

    cdate, 

   c_Week,  

    c_Week,

     channel,

    domain,

   stage,

   stage_no,

   sector,

   Booking_no,

     test_ids

drop table SEARCH;

Regards

Pardhu

[Anil_Babu_Samineni]

I don't even feel performance issue while using this on front-end. Can you share your rows in that object

Count(DISTINCT if(cdate>= vStartdate and cdate<=vEnddate and stage_no=1, cookie))

Changed to below and Try from your end then check how index seems in GDB{Governance Dashboard} -  {If available}.

Count({<cdate = {">= $(vStartdate) <= $(vEnddate)"}, stage_no={'1'}>} DISTINCT cookie)

The reason i am feeling change is, Aggregation good and works too in script level. But, It effect to the DM

[saradhi_it]

Actually the data was historical and we are thinking if the data was Aggregation in the backend we are assuming it will decrease dashboard size

[Anil_Babu_Samineni]

True, I agree for your way.

But, In script i didn't feel any wrong. So, I will explain you one thing

Recently(8 Months back) i did Aggregation on script only. After that i removed that Pivot / Straight objects and used Table box for Aggregation fields simply and then It showing Out of memory with X Mark.

Hope clear and helps to you

[saradhi_it]

the set analysis was not working showing zero values

[Anil_Babu_Samineni]

It should work, Can you share the snap where it is not working