Skip to main content

Hi Community

 

I have the following PQL Query as an KPI:

 

AVG (

  CASE

    WHEN

      MATCH_ACTIVITIES ( NODE 'Proposal Submitted' ] ) = 1

    THEN

        CALC_THROUGHPUT (

          First_OCCURRENCE 'Proposal Stage' ]

          TO

          Last_OCCURRENCE 'Proposal Submitted' ] ,

          REMAP_TIMESTAMPS ( "ACTIVITIES_TABLE_HUBSPOT"."EVENT_TIME" , DAYS )

        )

           ELSE

      'No Proposal was Submitted'

      END)

 

I would like to have the string value 'No Proposal was Submitted' for the business in thr ELSE statement. I know I can simply replace it with the INT value 0, but I don't want that.

 

Any help is appreciated 😊

 

The error reads: Operator requirements are not met. CASE WHEN expression is not compatible with an ELSE case of data type STRING when at least one THEN case is of data type INT

 

Kind regards,

Sverre Klein

 

 

Hi Sverre,

 

I would convert the average to string value (with proper rounding), so all CASE WHEN results are strings.

 

Regards,

Krzysztof

Hi Sverre,

 

I would convert the average to string value (with proper rounding), so all CASE WHEN results are strings.

 

Regards,

Krzysztof

Hi @krzysztof.papli12,

 

Thanks for your reply.

 

How would you go about this in the code above?

I have another opinion: In short I don't know if your approach makes sense from a logical (business point of view).

 

Your aim is to calculate an average throughputtime if a certain condition applies.

What your code aims at is to evaluate the CASE WHEN in the inner part and then build the average around it.

 

But I think its not a straightforward question on how to integrate those cases which do not match 'Proposal Submitted'. Even if you would consider the troughputtime = 0 for those is that actually correct perspective? Is it not more like "there is no troughputtime for those"? you would obviously drive down the average troughputtime of the rest of the cases down by those who should not be considered if you just treat them a thoughputtime=0.

 

Lastly I am pretty sure that you can't build any kind of AVG(1.2, "somestring") and moreover I am in quite a doubt that you could do a AVG("1.2", "somestring").

 

My solution approach would be to use the context of the PQL.Think about moving the CASE WHEN into a FILTER statement.

Or 2nd approach: if it needs to be one single PQL you could do the AVG by hand: summing up all the Throughputtimes and then dividing by a COUNT of all the relvant Items.

 

I have another opinion: In short I don't know if your approach makes sense from a logical (business point of view).

 

Your aim is to calculate an average throughputtime if a certain condition applies.

What your code aims at is to evaluate the CASE WHEN in the inner part and then build the average around it.

 

But I think its not a straightforward question on how to integrate those cases which do not match 'Proposal Submitted'. Even if you would consider the troughputtime = 0 for those is that actually correct perspective? Is it not more like "there is no troughputtime for those"? you would obviously drive down the average troughputtime of the rest of the cases down by those who should not be considered if you just treat them a thoughputtime=0.

 

Lastly I am pretty sure that you can't build any kind of AVG(1.2, "somestring") and moreover I am in quite a doubt that you could do a AVG("1.2", "somestring").

 

My solution approach would be to use the context of the PQL.Think about moving the CASE WHEN into a FILTER statement.

Or 2nd approach: if it needs to be one single PQL you could do the AVG by hand: summing up all the Throughputtimes and then dividing by a COUNT of all the relvant Items.

 

Thanks for your extensive answer @manuel.wetze 😁

 

It is indeed the example you proposed that "there is no throughput time". I moved the CASE WHEN statement into a FILTER with the appropriate logic. This solved it!

Reply


Community Code of Conduct