topic Re: Format value based on another dimension in App Development

Format value based on another dimension

Fri, 28 Oct 2016 21:18:16 GMT

Hello,

I am somewhat new to Qlik Sense and am setting up a pivot table. My data is set up in such a way that there is a value field with a number, and an "Attribute" field. The "Attribute" field is being used to list out the values. An example of the data is shown below, and I would like to reflect the formatting similarly in the pivot table.

Attribute	Value
01: Sales	100,000.00
02: Cost of Sales	90,000.00
03: Gross Margin	10,000.00
04: Operating Expenses	5,000.00
05: Operating Margin	5,000.00
06: OM %	5.00%
07: Adjusted %	3.00%
08: Adjustment	(2,000.00)

After playing around with the expression formulas for the value, I have found that it is possible to determine formatting via formula, however I am having trouble with formulating the logic to make it format based on the Attribute.

This is as far as I have gotten, but it is currently reading the condition as false, and thus formatting everything as a percentage.

If(

Attribute="01: Sales",

Num(Sum(Value)/1000,'#,##0'),

Num(Sum(Value),'#,##0.00%'))

Is it possible to format conditionally in a pivot table?

Any thoughts are quite welcome.

Warm Regards,

Briana

Re: Format value based on another dimension

Oleg_Troyansky — Fri, 28 Oct 2016 22:12:25 GMT

Hi Briana,

the only thing that appears to be wrong in your formula is the use of double quotes for the string '01: Sales' instead of single quotes. So, I'd correct the formula this way:

If(

Attribute='01: Sales',

Num(Sum(Value)/1000,'#,##0'),

Num(Sum(Value),'#,##0.00%'))

Let me just mention as a side comment that this calculation will get a lot slower than it should be, especially when you add all other options. Qlik Sense will calculate all the possible formulas and show one of the results to you. If your data is very small, than this may not be a concern, otherwise, I'd look for other ways of reaching the same effect.

cheers,

Oleg Troyansky

Learn advanced QlikView and Qlik Sense techniques in my book QlikView Your Business

Re: Format value based on another dimension

Sat, 29 Oct 2016 01:59:29 GMT

Hi Oleg,

Thank you so much for your help, it worked and everything is much better to look at. My dataset is very small, fortunately, so this does not cause me any issue in this case.

Thanks so much!

Warm Regards,

Briana