Qlik Sense Dynamic numeric formatting in a pivot table

alexis — Tue, 30 Nov 2021 18:17:33 GMT

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

topic Qlik Sense Dynamic numeric formatting in a pivot table in App Development

Qlik Sense Dynamic numeric formatting in a pivot table