Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Conditional Calculated Value in Table Chart Column?

(I have searched for this answer but have not found anything applicable to my situation.) I have a column in a table chart whose values should only be calculated if their status is "Submitted", "Resubmitted" or "In Approval"; otherwise it should display a null value. Can someone help me understand the proper syntax required for this and in which field I need to place it? This is as far as I've gotten with the expression guessing and obviously it's not working:

=if({status_name='Submitted','Resubmitted','In Approval'}, (num(((today()+1)-submit_date),'#,##0.00')),1,0)

Thanks in advance.

1 Solution

Accepted Solutions

Re: Conditional Calculated Value in Table Chart Column?

Hi,

Check the set analysis expression:

Sum({< status_name = {'Submitted', 'Resubmitted', 'In Approval'} >} ((today()+1)-submit_date))

Then set the number format in the chart properties, Number tab.

Hope that helps.

Miguel

4 Replies

Re: Conditional Calculated Value in Table Chart Column?

Hi,

Check the set analysis expression:

Sum({< status_name = {'Submitted', 'Resubmitted', 'In Approval'} >} ((today()+1)-submit_date))

Then set the number format in the chart properties, Number tab.

Hope that helps.

Miguel

Not applicable

Re: Conditional Calculated Value in Table Chart Column?

Well that works. In the interest of keeping the QV community clean of redundant posts - the cells that don't meet the criteria are correctly showing "0.00". But is there a way to change the zeroes to hypens or blanks? If not it's fine, just wondering.

Re: Conditional Calculated Value in Table Chart Column?

Hi,

By default aggregation functions in charts show zero even when there is no record in the source. So a Sum() for a month that does not have values will show "0".

Anyway, in the chart properties, Presentation tab, you can play with the Suppress Zero Values, and populate null/missing values and set according to your needs.

Hope that helps.

Miguel

Not applicable

Re: Conditional Calculated Value in Table Chart Column?

What I settled on was setting the "Number Format Settings" to "Number" for this expression, with a precision of 3. This gave me the required 2 decimal places for the calculated values and a single "0" for all the others.

Community Browser