Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
(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.
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
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
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.
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
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.