Help for Summary filtering on 2 calculated summary fields?

I am hoping I have missed how to filter a summary based on its measurements. I haven't found something that addresses my issues in the forum yet, but perhaps I missed it.

I am currently exporting the entire summary to Excel and then trying to filter the data in Excel. However, directly calculating on any exported data in Excel causes our Excel files to corrupt and the calculations are usually deleted from the spreadsheet anyway. Rather than put back the formulas every day or copy paste the data to another file to work on, our work arounds, for now, are to do as much as possible in Monarch before exporting to Excel. So...

I would like to have the summary only show me records whose max(value)<>avg(value). And I'd love to do it in 1 step. Have I missed how to do that or can it not be done?

Using Monarch Pro 11.9.1.0.

Thanks for your time and input!

Would like this summary to only show line #2 from our StoreNo 316.

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Altair Forum User

Hi Tiffany,

I think you can do this with a calculated field in the summary followed by a matching limit.

First, add a new measure column to your summary. Either the PageFromFooter or LastPageFromFooter will be fine.
Edit the Formula for that field and change it to “Max([PageFromFooter]) - AVG([LastPageFromFooter])”
You can change the name or hide it on the general tab if you want to.
Select the DateFrom field and go to the Matching tab.
Choose a MeasureLimit of [YourNewField] <> 0 and select the “Hide but include in totals” option.

Regards,

Steve.

Altair Forum User

Hi Tiffany,
I think you can do this with a calculated field in the summary followed by a matching limit.
First, add a new measure column to your summary. Either the PageFromFooter or LastPageFromFooter will be fine.
Edit the Formula for that field and change it to “Max([PageFromFooter]) - AVG([LastPageFromFooter])”
You can change the name or hide it on the general tab if you want to.
Select the DateFrom field and go to the Matching tab.
Choose a MeasureLimit of [YourNewField] <> 0 and select the “Hide but include in totals” option.

Regards,
Steve.

Nice one Steve!

Altair Forum User

Hi Tiffany,
I think you can do this with a calculated field in the summary followed by a matching limit.
First, add a new measure column to your summary. Either the PageFromFooter or LastPageFromFooter will be fine.
Edit the Formula for that field and change it to “Max([PageFromFooter]) - AVG([LastPageFromFooter])”
You can change the name or hide it on the general tab if you want to.
Select the DateFrom field and go to the Matching tab.
Choose a MeasureLimit of [YourNewField] <> 0 and select the “Hide but include in totals” option.

Regards,
Steve.

Or choice #3! I just can't see how to do it. Thrilled that it can be done and already implemented this am. Thanks very much!!!!

Altair Forum User

Hi Tiffany,
I think you can do this with a calculated field in the summary followed by a matching limit.
First, add a new measure column to your summary. Either the PageFromFooter or LastPageFromFooter will be fine.
Edit the Formula for that field and change it to “Max([PageFromFooter]) - AVG([LastPageFromFooter])”
You can change the name or hide it on the general tab if you want to.
Select the DateFrom field and go to the Matching tab.
Choose a MeasureLimit of [YourNewField] <> 0 and select the “Hide but include in totals” option.

Regards,
Steve.

Steve, may I have your brains again perhaps? I want to do something just like this in V13.4. Unfortunately, an upgrate broke all of our dinosaur architecture here and we all had to update Monarch & Excel...I could barely get around in V11 and now I'm just useless in V13. I want to sort by the abs of a summary calculation. Have you expanded your knowledge to the later versions??????

I printed the manual, but finding work time to go through all 400+ pages is not gonna happen anytime soon.

I'll be waiting...THANK YOU, THANK YOU!?!?!?