Calculated dimension total

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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..

Re: Calculated dimension total

neelamsaroha157 — Tue, 20 Dec 2016 18:14:06 GMT

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

Re: Calculated dimension total

nareshthavidishetty — Tue, 20 Dec 2016 18:28:34 GMT

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);

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..

topic Re: Calculated dimension total in QlikView

Calculated dimension total

Re: Calculated dimension total

Re: Calculated dimension total