topic Set Expression not working with aggregation function in App Development

Set Expression not working with aggregation function

dr_suresh_baabu — Sun, 25 Oct 2020 11:43:17 GMT

I have two tables- Master and Study Table. In master table, there are two columns (MasterID and a MeasureColumn. In StudyTable, there are 4 columns-MasterID, StudyID, Site and Month. Two tables are linked with MasterID. One MasterID can have multiple StudyID in study Table. so the requirement is to aggregate the count with MasterID.

The below expression is working fine as expected

= sum(aggr(sum(MeasureColumn),MasterID))

But when I tried along with a set expression as below it is not working as expected. I wanted to exclude the Month selection in the expression. In my chart, the dimension is Site and the measure is the below.

sum(aggr(sum( {<Month=>} MeasureColumn),MasterID))

sum(aggr({<Month=>}sum(MeasureColumn),MasterID))

Re: Set Expression not working with aggregation function

Kushal_Chawda — Sun, 25 Oct 2020 11:53:46 GMT

@dr_suresh_baabu you should exclude the selection in outer aggregation as well

sum({<Month=>} aggr(sum( {<Month=>} MeasureColumn),MasterID))

Re: Set Expression not working with aggregation function

dr_suresh_baabu — Sun, 25 Oct 2020 12:09:32 GMT

Thank you! That worked for exclusion of field.

I also need to pass a dynamic Month range based on a selection on a month. Surprisingly, both the expressions worked

1. = sum( {<Monthnum={">=$(=(vStart))<=$(=(vEnd))"}, Month=>} aggr( {<Month=>} sum(MeasureColumn),MasterID))

2. = sum( {<Monthnum={">=$(=(vStart))<=$(=(vEnd))"}, Month=>} aggr( {<Monthnum={">=$(=(vStart))<=$(=(vEnd))"},Month=>} sum(MeasureColumn),MasterID))