Help with filtering pivot table rows by multiple input variables

AP2 — Thu, 27 Oct 2022 07:38:26 GMT

Good day all,

I have a pivot table with the following structure:

- Row: Customer ID

- Column: Year Month

- Measures: sales values in month, no. of orders, rolling average spend (per customer). These are pre - aggregated in the data I bring in, with year month in a single column with corresponding values in the other columns (Customer Id, Sales in the month, number of order etc.). I used a pivot table so I can easily transpose this data.

The goal is for the user to be able to filter out customers that meet the criteria in the input variables I added - minimum sales per month, minimum number of orders and minimum rolling avg.

I created the input variables, and applied the following login to the customer ID row:

if
(month sales >= $(vMinSalesVariable)
and
no of orders >= $(vMinOrdersVariable)
and
Rolling average of sales >= $(vMinRollingAvgVariable)

Customer ID)

The Issue I'm having is that rather than filtering for customers that meet the minimum variables for all months in my table, it only filters for those who meet them for at least on month and grays out the values in the other months.

Any assistance with this will be greatly appreciated.

Re: Help with filtering pivot table rows by multiple input variables

Mario_De_Felipe — Thu, 03 Nov 2022 16:49:56 GMT

If you want the condition was accomplised every month you have to do something like this using AGGR:

if(AGGR(SUM(if(
(month sales >= $(vMinSalesVariable)
and
no of orders >= $(vMinOrdersVariable)
and
Rolling average of sales >= $(vMinRollingAvgVariable)

1,0)),[Month],[Customer ID])=AGGR(Count(distinct Month),[Customer ID])

,Customer ID)

Re: Help with filtering pivot table rows by multiple input variables

AP2 — Thu, 24 Nov 2022 07:55:43 GMT

Thank you so much, will try this out.

topic Re: Help with filtering pivot table rows by multiple input variables in App Development

Help with filtering pivot table rows by multiple input variables

Re: Help with filtering pivot table rows by multiple input variables

Re: Help with filtering pivot table rows by multiple input variables