Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Hi, Is there any specific reason that you are using the expression as calculated dimension and not as measure?
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..