topic Re: Set analysis expression to include all years in App Development

Set analysis expression to include all years

mikegrattan — Thu, 05 Jul 2018 22:02:55 GMT

I want to modify an expression so it will include all years, and all warehouses for that matter, but it doesn't seem to be working.

Avg(Aggr(Avg({$<Year=,Warehouse=>} MinutesToLoad), SalesOrder))

The expected behavior is that my Distribution Plot chart should always include all years and all warehouses, even if selections have been made in the app. But, when I tested it by selecting a single year, the chart filtered to just that year. Any ideas on what I'm doing wrong?

Thanks.

Re: Set analysis expression to include all years

petter — Thu, 05 Jul 2018 22:15:04 GMT

You might have to actually repeat the set expression both for the Aggr()-function and the outer Avg()-function for this to work as you expect.

Re: Set analysis expression to include all years

mikegrattan — Fri, 06 Jul 2018 14:24:16 GMT

I'm not sure if I did this right, but here's the modified expression:

Avg({$<Year=,Warehouse=>}Aggr({$<Year=,Warehouse=>} Avg({$<Year=,Warehouse=>} MinutesToLoad), SalesOrder))

It works if I select something from the Year dimension, but once I select a Warehouse the chart filters on the Warehouse. So, it partially works which is an improvement!

Anything else I can do to get it work better?

Re: Set analysis expression to include all years

sunny_talwar — Fri, 06 Jul 2018 14:28:41 GMT

Try this

Avg({$<Year, Warehouse>}Aggr(Avg({$<Year, Warehouse>} MinutesToLoad), SalesOrder))

Re: Set analysis expression to include all years

mikegrattan — Fri, 06 Jul 2018 17:38:05 GMT

Still working for Year dimension,but not Warehouse dimension. When I filter on a Warehouse it starts behaving strangely, such as a non-selected warehouse will show up in addition to the selected warehouse, but I just want all warehouses to show up.

Re: Set analysis expression to include all years

sunny_talwar — Fri, 06 Jul 2018 18:12:12 GMT

Would you be able to share a sample or sample image to show this? Are you using a calculated dimension for Filter and have you made sure that the field name is Warehouse and not WAREHOUSE or warehouse?

Re: Set analysis expression to include all years

mikegrattan — Fri, 06 Jul 2018 20:49:12 GMT

Warehouse is a calculated dimension but I have also tried with just the Warehouse dimension and get the same behavior. When using the calculated dimension, the calculation is:

=If(Match(Warehouse,'HUR','SAL','SSS','SBF','YUM','YBF','YSS'),Warehouse,Null())

I also have this stored in a Master dimension called PFWarehouse.

First screen grab shows the calculated dimension, with no warehouses selected

Second screen grab shows the calculated Warehouse dimension with only SAL selected but HUR also shows up.

Third screen grab shows the normal Warehouse dimension with no warehouses selected:

And the last screen grab shows the normal Warehouse dimension, with the SAL warehouse selected but BRD and HOL show up instead.

Note: Since my expression for the calculated dimension adds the possibility of a Null result, I will be unchecking the box in the Warehouse dimension that includes null values.

Re: Set analysis expression to include all years

sunny_talwar — Sat, 07 Jul 2018 02:19:25 GMT

May be create the filter like this

=Aggr(Only({1<Warehouse = {'HUR', 'SAL', 'SSS', 'SBF', 'YUM', 'YBF', 'YSS'}>} Warehouse), Warehouse)

and then see if the selection in the above filter is ignored by the chart or not

Re: Set analysis expression to include all years

mikegrattan — Mon, 09 Jul 2018 14:00:05 GMT

Very interesting approach! Unfortunately, I'm still getting the same behavior.

Re: Set analysis expression to include all years

sunny_talwar — Mon, 09 Jul 2018 14:03:35 GMT

Can you try this with a new field in the script

If(Not Match(Warehouse, 'HUR', 'SAL', 'SSS', 'SBF', 'YUM', 'YBF', 'YSS'), Warehouse, Null()) as New_Warehouse

and not ignore selection in New_Warehouse and use this as your filter?

Re: Set analysis expression to include all years

mikegrattan — Mon, 09 Jul 2018 15:58:51 GMT

Still getting strange results. Adding this expression to the data load as a new field limits the New_Warehouse values to those that are not in that list...so I'm not really sure how to use that to "not ignore the selection in New_Warehouse" and use it as a filter. If I add New_Warehouse to a filter panel, the only options in that filter are HOL and OXN.

Re: Set analysis expression to include all years

sunny_talwar — Mon, 09 Jul 2018 16:06:29 GMT

Sorry, this was my bad... I think you needed Match and not "Not Match"

If(Match(Warehouse, 'HUR', 'SAL', 'SSS', 'SBF', 'YUM', 'YBF', 'YSS'), Warehouse, Null()) as New_Warehouse

Re: Set analysis expression to include all years

MK9885 — Mon, 09 Jul 2018 16:15:04 GMT

Not sure if this may help since experts in Set analysis are helping you but maybe try below...

syntax may be wrong but to ignore any selections made on chart use 1 and to show all the years and warehouse regardless of selections use "*"

Avg(Aggr(Avg({ 1 <Year={"*"} >+< Warehouse={"*"}>} MinutesToLoad), SalesOrder))

Re: Set analysis expression to include all years

mikegrattan — Mon, 09 Jul 2018 19:25:53 GMT

I've updated the load script with the following:

If(Match(Warehouse,'HUR','SAL','SSS','SBF','YUM','YBF','YSS'),Warehouse,Null()) as PFWarehouse,

(Got rid of the Master Dimension that was defined the same way).

And I'm still using the vAvgMinToLoadPerOrder variable defined as:

Avg({$<Year,PFWarehouse>}Aggr(Avg({$<Year, PFWarehouse>} MinutesToLoad), SalesOrder))

I also used the PFWarehouse dimension for my filter pane on the sheet with the chart in question. Unfortunately, I'm still getting the same behavior.

Re: Set analysis expression to include all years

mikegrattan — Mon, 09 Jul 2018 19:28:10 GMT

Thanks for your suggestion. I tried it and got different behavior than noted before, but I'm still not getting all warehouses. Instead, I'm getting the filtered warehouse, but at least I'm not getting additional noise such as a warehouse that was not filtered.

Re: Set analysis expression to include all years

sunny_talwar — Mon, 09 Jul 2018 19:32:58 GMT

That is very strange... is it possible to share your qvf (here or through email)?

Re: Set analysis expression to include all years

mikegrattan — Tue, 10 Jul 2018 14:09:14 GMT

Here's a copy of the QVF. The data that I'm looking at comes from the Cooler table, ('LOAD' status from the load sections called CoolOut 2014-2016 QVD, Cooler Outbound 2017 QVD, and Cooler Outbound 2018). The chart I'm working on is on the Customers sheet.

Thanks.

Re: Set analysis expression to include all years

sunny_talwar — Tue, 10 Jul 2018 15:51:07 GMT

It seems like a bug to me (or I am missing a configuration which I am not aware of).... If I convert the same chart to a table... the selection in PFWarehouse are ignored.... but not in box plot. Also, what is weird is that if I select 1 value, the box plot shows 2 warehouse... if I select 4, it will show 5.... based on the sort order. This means that it has nothing to do what what is selected... for example if you sort in ascending order and you select YUM, the chart is showing HUR and SAL... Since, I have selected YUM, I would have expected to see just YUM, if set analysis wasn't working.... it just seem to be something which is beyond my understanding.

Re: Set analysis expression to include all years

mikegrattan — Tue, 10 Jul 2018 16:51:03 GMT

Ok, thank you for looking at it. I'll submit it to tech support and see if it's possibly a bug.