Calculated dimension total

[nareshthavidishetty]

Hi,

As per the report design we have calculated the % as calculated dimension ,but now the requirement is to get the total.

In Below image colored column is the calculated dimension.

calculated dimension:

=aggr(num((sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales))

/(sum({<Date={$(varMaxDate)},flag={'Wet Sign'}>} Sales)+sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales)

+sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Not Complete'}>} Sales)),' #,##0.0%'),Loc)

Thanks..

[neelamsaroha157]

Hi, Is there any specific reason that you are using the expression as calculated dimension and not as measure?

[nareshthavidishetty]

Hi,

As per the requirement we have designed an data model but with that design it is not possible to calculate % in expression.Because the expression has single logic which is categorized by flags.

Below is the report design and script.

Script:

Temp:

LOAD

     'eSign' as flag,

     A7AJCD,

      A7AACD as Loc,

     A7AIDT as Date,

     ESIGN_COMPLETE_NEW as Sales,

     'Complete'&'-'&ESIGN_COMPLETE_NEW as Category1,

     APA0TX

FROM

(qvd)Where Len(Trim('Complete'&'-'&ESIGN_COMPLETE_NEW)) > 0;

Concatenate

LOAD

  'eSign' as flag,

  A7AACD as Loc,

     A7AJCD ,

     A7AIDT as Date,

    'Not Complete'&'-'& ESIGN_NOT_COMPLETE_NEW as Category2,

    ESIGN_NOT_COMPLETE_NEW as Sales

FROM

(qvd)Where Len(Trim('Not Complete'&'-'& ESIGN_NOT_COMPLETE_NEW)) > 0;

NoConcatenate

load * ,left(Category1,8) as Category Resident Temp;

Concatenate

load * ,left(Category2,12) as Category Resident Temp;

join

Tempp: 

Load 

               min(Date) as minDate, 

               max(Date) as maxDate 

Resident Temp; 

Let varMinDate = Num(Peek('minDate', 0, 'Tempp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Tempp')); 

drop Table Temp;

Concatenate

LOAD A7AJCD,

     'Total Orders' as flag,

     A7AACD as Loc,

     '' as Category,

     A7AIDT as Date,

     TOTAL_ORDERS_DAILY_NEW as Sales,

     APA0TX

FROM

(qvd);

A:

Concatenate

LOAD

  'Wet Sign' as flag,

  A7AACD as Loc,

     A7AIDT  as Date,

     A7AJCD ,

     BVA5ST as Category,

      Pick(Match(H2P9TX, 'GENERAL ELECTRIC','IL 12MO CSH OPT')+1, Capitalize(H2P9TX), 'General Electric\Bank','COP') as H2P9TX ,     

   WET_SIGN_DAILY as Sales

FROM

(qvd)  Where Len(Trim(BVA5ST)) > 0;

Concatenate

LOAD  'Non-eSign' as flag,

  A7AACD as Loc,

     A7AIDT  as Date,

     A7AJCD ,

     BVA5ST as Category,

      Pick(Match(H2P9TX, 'GENERAL ELECTRIC','IL 12MO CSH OPT')+1, Capitalize(H2P9TX), 'General Electric\Synchrony Bank','COP') as H2P9TX ,

     NON_ESIGN as Sales

FROM

(qvd);

Thanks..