Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Miguel_Angel_Baeyens

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

View solution in original post

4 Replies
Miguel_Angel_Baeyens

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
Author

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.

Miguel_Angel_Baeyens

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
Author

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.