Trying to convert the return value from an IF statement to an integer

[JacobJD22]

Hello Community,

I am trying to create a chart that shows the billable units per hour in 2 week intervals for my organization. I have tried using different iterations of the following expression for my measure:

if(MATCH([Services.Service Name],'Brief Visit-PN','Client Visit - BA','Client Visit - Peer','Client Visit-PN','Client Visit-PNTPG','Closed Review','Collateral Visit - In Reach','Collateral Visit-CTCLOG','Collateral Visit-PN','Collateral Visit-PNTPG','Contact Log','Coordination of Care','Discharge Summary','Med Visit - PSYNOTE - Non E&M','Nurse Visit-NNOTE','Transition Plan'),
num#(sum([Services.Duration]))/15)

One unit equals 15mins for my organization which is why I am dividing by 15. When I apply the above expression I get the "The chart is not displayed because it contains only undefined values" message. From what I understood this message pops up when non numeric values are returned which is why I tried using num# to convert the return from the IF statement. Is there a better way to go about this? Even with the num function I still get the same message from the chart. It would also be helpful to know the best way to group data in two week periods. The weekstart function has gotten me part of the way but I don't know how to extend it to two weeks.

Thank you in advance.

[Happy_Mask_Salesman]

Hi Jacob,

Do you mind sharing with us some values from the field [Services.Duration]?

I believe somehow you are failing at the conversion using num#(), and the result of the function is giving zero, while you are dividing by 15, which results into an impossible real number.

Check the num#() parameters, as you might have to specify the decimal and thousand separator if they are different that your Qlik Sense default number format.

https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpretati...

Try different combinations:

alt( (num#(sum([Services.Duration]), '###0,00')/15), 0)

or

alt( (sum(num#([Services.Duration], '###0,00'))/15), 0)

Alt() function is also helpful, if your first parameter doesn't give you a valid numerical representation, it will set to the second parameter specified, for example, a simple zero.

About grouping data in two week period, I would suggest you the use of a master calendar table with Week field, associate it with your date values and help yourself with set analysis.

For a chart, with dimension Week, an expression similar to this:

alt( (num#(sum({<Date={">=$(=Max(Date)-14)"}>}[Services.Duration]), '###0,00')/15), 0)

Regards,

[JacobJD22]

Sure,

Some values from the [Services.Duration] field include: 0, null values, 15, 150, 45. I tried using some of your suggestions and although the alt() function does get the chart to generate, it only generates with the alternate value (in this case 0). Without if() the expression works without any issues. The issue is I only want the duration from certain services to get counted. Is there a better way of only counting certain services without using if()?  Below are some of the alternate expressions I tried:

if(MATCH([Services.Service Name],'Brief Visit-PN','Client Visit - BA','Client Visit - Peer','Client Visit-PN','Client Visit-PNTPG','Closed Review','Collateral Visit - In Reach','Collateral Visit-CTCLOG','Collateral Visit-PN','Collateral Visit-PNTPG','Contact Log','Coordination of Care','Discharge Summary','Med Visit - PSYNOTE - Non E&M','Nurse Visit-NNOTE','Transition Plan'), alt(sum(num#([Services.Duration], '###0,00'))),0)

alt(num#(if(MATCH([Services.Service Name],'Brief Visit-PN','Client Visit - BA','Client Visit - Peer','Client Visit-PN','Client Visit-PNTPG','Closed Review','Collateral Visit - In Reach','Collateral Visit-CTCLOG','Collateral Visit-PN','Collateral Visit-PNTPG','Contact Log','Coordination of Care','Discharge Summary','Med Visit - PSYNOTE - Non E&M','Nurse Visit-NNOTE','Transition Plan'), [Services.Duration]), '###0,00'), 0)

[Happy_Mask_Salesman]

Forget the if, instead of using it, let's use set analysis instead, one of the best tools tools that Qlik has.

Tell us about this formula:

Sum({<[Services.Service Name]={'Brief Visit-PN','Client Visit - BA','Client Visit - Peer','Client Visit-PN','Client Visit-PNTPG','Closed Review','Collateral Visit - In Reach','Collateral Visit-CTCLOG','Collateral Visit-PN','Collateral Visit-PNTPG','Contact Log','Coordination of Care','Discharge Summary','Med Visit - PSYNOTE - Non E&M','Nurse Visit-NNOTE','Transition Plan'}>}alt(num#([Services.Duration]), 0))/15

Try different combinations for this formula. Also make sure this services have values on them.

I would create a straight table, with Service.Service Name as dimension, an also add an expression such as alt(num#([Services.Duration]), 0)) and the field Services.Duration to check if the alt() and num() functions are working correctly at converting the text into number.

Regards,