topic Re: How to Group By Sum Expression in App Development

How to Group By Sum Expression

jamiemcc — Wed, 21 Sep 2022 13:07:59 GMT

Hi Guys,

I want to group By [TaskType] on the below Syntax but unsure how to write it?

Sum([Estimated Rating Time])/ sum(NumberofResults)

Any help greatly appreciated.

Regards,

Jamie

Re: How to Group By Sum Expression

rubenmarin — Wed, 21 Sep 2022 13:15:19 GMT

Hi, this is on chart expression? If it's for a table with other dimensions and you want to ignore dimesnions and group on the upper level you can use total:

Sum(TOTAL <[TaskType]>[Estimated Rating Time])/ sum(TOTAL <[TaskType]>NumberofResults)

Re: How to Group By Sum Expression

jamiemcc — Wed, 21 Sep 2022 13:27:52 GMT

Hi Rob,

Thanks - Yes this is a pivot table where I want to show when the ERT changes throughout the month.

My only Concern is I should be getting a result of 300 for Sept and Aug for the Task Type I'm selecting but getting below,

Am I missing something?

SELECT Sum(`Estimated Rating Time`)/sum(NumberOfResults) FROM liox_dp_bus.vwDetailedPayments where TaskType - 'Photo Search Satisfaction' and FinishTime between '2022-08-01 00:00:00' and '2022-09-30 23:59:59'

In the database the query above shows 300.

Thanks,

Jamie

Re: How to Group By Sum Expression

rubenmarin — Wed, 21 Sep 2022 13:32:59 GMT

Using total will ignore all dimensions, if you select Sep-2022 and Aug-2022 from the monthyear field it should return the same as the SQL. Without seleceting anything it is calculating the value tor the tasktype using all the MonthYear values

Re: How to Group By Sum Expression

jamiemcc — Wed, 21 Sep 2022 13:42:51 GMT

Hi Rob,

If I select those values it returns 152.1 - Do I need to Group by Monthyear in my calculation?

It looks like its not taking the month into Consideration.

Regards,

Jamie

Re: How to Group By Sum Expression

rubenmarin — Wed, 21 Sep 2022 13:52:24 GMT

If you group also by year in that table is like no grouping, it will be the same as "Sum([Estimated Rating Time])/ sum(NumberofResults)".

Try to get a lower amount of data to compare the values of the sql and qlik row by row, maybe the datamodel in Qlik is not using the same row values as they are in database