topic Re: Conditional Calculated Value in Table Chart Column? in QlikView

Conditional Calculated Value in Table Chart Column?

Tue, 11 Dec 2012 13:10:14 GMT

(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.

Re: Conditional Calculated Value in Table Chart Column?

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 13:19:27 GMT

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

Re: Conditional Calculated Value in Table Chart Column?

Tue, 11 Dec 2012 14:43:52 GMT

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?

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 15:21:51 GMT

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

Re: Conditional Calculated Value in Table Chart Column?

Tue, 11 Dec 2012 15:25:43 GMT

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.