Qlik Community

Qlik Sense App Development

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

Not applicable

Format value based on another dimension

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.

  

AttributeValue
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

1 Solution

Accepted Solutions
MVP
MVP

Re: Format value based on another dimension

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

2 Replies
MVP
MVP

Re: Format value based on another dimension

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

Not applicable

Re: Format value based on another dimension

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