topic Re: sum() ignoring filters in App Development

sum() ignoring filters

charlie2 — Tue, 27 Apr 2021 08:01:07 GMT

Hi,

I have a data set with sales per sales rep (RSM), year, and article code, in one app.

I would like to set up a KPI which shows the total sales of a particular RSM and a particular year. Apparently, it seems an easy job: filter by RSM and filter by year. However, I would like to still show the total sales of the selected RSM and selected year, even when I have filtered by a specific article code.

I know I could do it through the alternative states. Nevertheless, I need these for other purposes.

I have tried the following formula for the KPI, but I, unfortunately, do not manage to get the desired result:

Formula:

sum({<Year= {$(=Max(Year))}, [RSM] = {$(=GetFieldSelections[RSM])}>} Sales)

Result:

sum({<Year= {2021}, [RSM] = {}>} Sales)

I would appreciate if anyone knows how to solve this problem.

Re: sum() ignoring filters

agigliotti — Tue, 27 Apr 2021 08:45:23 GMT

Hi @charlie2 ,

To me it's not clear what you are trying to achieve.

What do you mean with "filter a specific article code" ?

which are your fields selections in that scenario ?

what's the actual result and the expected one?

Best Regards

Re: sum() ignoring filters

charlie2 — Tue, 27 Apr 2021 11:06:43 GMT

Hi @agigliotti

Thanks for your quick reply!

So my possible filters are: Article Code, RSM and Year. I would like to use these three filters, but in the KPI only show the sum of Sales from the filtered RSM and Year, therefore ignoring the filter from the Article Code.

At the moment, my result takes the sum of Sales of the filtered Article Code, RSM and Year.

Best regards,

Re: sum() ignoring filters

agigliotti — Tue, 27 Apr 2021 12:32:29 GMT

You could use the below expression to ignore the Article Code field selections:

sum( {< [Article Code] >} Sales )

I hope it can helps.

Best Regards

Re: sum() ignoring filters

charlie2 — Wed, 28 Apr 2021 07:57:45 GMT

Hi @agigliotti ,

Thanks for your proposal. Unfortunately it doesn't work but I found the following solution, which it seems to deliver the right figures:

sum({1<Year= {$(=Max(Year))}, [RSM] = {'$(=GetFieldSelections([RSM]))'}>} Sales)

Best,