topic Re: Aggregation expression difficulties in App Development

Aggregation expression difficulties

Brunommsilva — Thu, 06 Oct 2022 17:03:12 GMT

Hello,

Been struggling to replicate an expression that the costumer needs. Excel attached.

I need an expression to get the YTD value (which is the 87% in the orange cell) on a Pivot table where none of the relevant fields exist as Dimension except for Name and Product, which can be collapsed. I've tried innumerous combinations of Aggr expressions, but nothing seems to be working.

I would be very grateful if someone has a solution for this.

The Excel is attached. I'm not pasting the data here as it would be confusing to read.

Thanks in advanced!.

Bruno.

Re: Aggregation expression difficulties

justISO — Mon, 10 Oct 2022 06:43:12 GMT

Hi, it depends on how you get this 'example' data table, but from it you can get this 87,3% with formula:

sum(aggr( ((Sum([SALES MONTHLY])/Sum([TARGETS MONTHLY]))*[PROD WEIGHT])*(count([PROD WEIGHT]) / 7) ,[PRODUCT NAME],[PROD WEIGHT]))

where I'm not sure what this 'tYTD=7' means, so I added it by hand, but it can be replaced with measure, like count(total distinct DATAMONTH) or something else.

Re: Aggregation expression difficulties

Brunommsilva — Mon, 10 Oct 2022 13:41:25 GMT

Hi, Thank you very much.

You don't even imagine how many variations of this expression I tried. I was very close to it, but I guess I started running in circles.

Thanks once again!

Best Regards,

Bruno Silva.