topic Re: Max aggr expressions based on 2 dimensions in App Development

Max aggr expressions based on 2 dimensions

Fri, 22 Jul 2016 15:02:19 GMT

I am trying to display the min and max # of leads (by store) for the selected region.

Dimensions are REGION and Store, the metric is metric is the sum(leads)

The desired end result will show the user for THAT region the MAX Leads by store (for that particular region), and the Min leads by store (also for the particular region) and where the store selection fits on a guage.

I tried playing aggr() but i couldnt figure it out. When i make a selection, the max is always just a sum(Leads) and matches the store selection.

Re: Max aggr expressions based on 2 dimensions

sunny_talwar — Fri, 22 Jul 2016 16:00:47 GMT

I don't completely understand the requirement from what you have provided. Would you be able to provide a sample or some sample data with the expected output to help you better?

Re: Max aggr expressions based on 2 dimensions

Fri, 22 Jul 2016 16:08:05 GMT

Sure.

Store, Region, Leads

A, Midwest, 4

B, Midwest, 6

C, Midwest, 12

D, West, 15

E, West, 20

F, West, 22

The desired output is when the user selects Store B, there will be a bar gauge graph that has the minimum value shown as 4, and the max shown as 12.

_________________________________

|//////////////// |

---------------------------------------------------------

4 6 12

The / represents shading.

If the user selects Store C, then the gauge should look as such

________________________________

|////////////////////////////////////////////////////////|

---------------------------------------------------------

4 12

The gauge min and max values should then readjust (based on the expression) so when the user decides to look at store E, the min would now be 15, and the max would be 22.

Re: Max aggr expressions based on 2 dimensions

Fri, 22 Jul 2016 16:10:27 GMT

I should mention that there are multiple campaigns that roll into each Store's Sum(Leads), I overgeneralized the sample data to make it cleaner.

Store, Region, Leads, Campaign

A, Midwest, 2, CmpA

A, Midwest, 2, CmpB

B, Midwest, 2, CmpA

B, Midwest, 4, CmpB

C, Midwest, 10, CmpA

C, Midwest, 10, CmpB

Re: Max aggr expressions based on 2 dimensions

sunny_talwar — Fri, 22 Jul 2016 16:28:21 GMT

Can you try this:

Min({<Store, Region = p(Region)>} Leads)

Max({<Store, Region = p(Region)>} Leads)

Re: Max aggr expressions based on 2 dimensions

rubenmarin — Fri, 22 Jul 2016 16:31:57 GMT

Hi Jason, having Region and Store as dimensions you can get the max per region with:

Max(TOTAL <Region>Aggr(Sum(leads), Region, Store))

For the Min you only have to change 'Max' to 'Min'

To keep these values ignoring selections you can use:

Max({1} TOTAL <Region>Aggr(Sum({1} leads), Region, Store))

But it will make all stores to show on the table when one store is selected, if you want to keep only the selected stores and the values doesn't change you can use:

If(Count(Store)>0, Max({1}TOTAL <Region>Aggr(Sum({1} leads), Region, Store)))

Edit: When I posted this I wasn't able to see you post with the sample and I was thinking in a table, If you want a gauge the option given by Sunny is faster.

Re: Max aggr expressions based on 2 dimensions

Fri, 22 Jul 2016 16:53:21 GMT

neither of those options worked, however, this appeared to work..after 2 hours of trial and error.

Min({1}aggr({$}sum({$<Store = >}Leads),Store))

Re: Max aggr expressions based on 2 dimensions

sunny_talwar — Fri, 22 Jul 2016 17:02:39 GMT

Where are using this? in a table or somewhere else?

Re: Max aggr expressions based on 2 dimensions

Fri, 22 Jul 2016 17:03:39 GMT

In a gauge extension in QS.

Re: Max aggr expressions based on 2 dimensions

Sun, 24 Jul 2016 20:32:00 GMT

STEP 1:

Table:

Store	Region	Sales
A	MidWest	4
B	MidWest	6
C	West	12
D	West	15
E	West	22
F	West	22

STEP 2:

Bar Graph - ~Dimension = Store~

~Measure = Sum(Sales)~

STEP 3 (Bit Tricky):

Gauge - ~Measure = Sum(Sales)~

~[Min] =

Min({

$<Region = p({

$<Store= {$(=Store)}>

}Region),Store=>

}Sales)~

~[Max] =

Max({

$<Region = p({

$<Store= {$(=Store)}>

}Region),Store=>

}Sales)~

Note: [Min] & [Max] are in - Appearances > Presentation > Range Limit in Gauge Properties Panel.

STEP 4: Select Store in Bar Graph. See effects in Gauge.

Note: On No Selection Gauge with set its limits with default values, but that too can be handled accordingly.

Happy Qliking!