Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
Partner - Creator III
Partner - Creator III

Qlik Sense Dynamic numeric formatting in a pivot table

Hi all

I have created a "Self-service" report using a pivot table where the user select dimensions and measures from a list of available Dimensions and Measures. These are stored in code as follows (extract):

DIMENSIONS:
LOAD * INLINE [
DimenionArea,  Dimension,              Description,               DimensionNo
Organization,     MasterCategory,    Master Category,      1
Organization,     SubCategory,          Sub-Category,           2
];
//Periodic, Report_Month, Month, 6
//Periodic, Document Month, Month, 6
//Periodic, 'Document Month', Month2, 7


MEASURES:
LOAD * INLINE [
MetricType,    Metric,                                        MetricNo,  MetricFormula
Money,              Turnover(CY),                          1,                   Report_TurnoverCY
Money,              Turnover(LY),                           2,                   Report_TurnoverLY
Money,              Turnover(CY vs. LY),              3,                   Report_TurnoverCYvLY
Percent,            Turnover(CY vs. LY (%)),      4,                   Report_TurnoverCYvLY%
];

The "Metric Formula" is a series of Master Items that have in their definition the formatting of each master item (percent, money, integer). When I make my selections of what dimensions and measures I want on the report, the formatting that I defined when I creatred the master items is not obeyed. 

Because my report is dynamic, my measure expression on the pivot table is something of the order of:

=$(=FirstSortedValue([MetricFormula],[MetricNo],1))

and the label is:

=FirstSortedValue([Metric],[MetricNo],1)

For the formatting someone suggested incorporating something like:

 if (Getfieldselections(MetricType)='Percent',num(sum(per_sales)/100,'#,##0%' ),Num(sum(sales),'#,##0'))

Anyone know I can incorporate the above methodology (shown in italics) which in effect replaces "Sum(per_sales)/100" with my rather more complex  =$(=FirstSortedValue([MetricFormula],[MetricNo],1))

 

Labels (1)
0 Replies