Your are correct that Case functions only return the specified result value for the first Boolean expression to evaluate as True.
Which means that you'd need to use a different calculation to return multiple values instead of a single value. Such as:
List ( If ( DM&DY = CM&CY;2 ) ; If ( DY=CY;1 ) ; If ( DY=CY-1;0))
This returns a list of values separated by return characters. If record is same month, same year, you'd get:
Same year, different months:
You can then use Not IsEmpty ( FilterValues ( Expenses::Status Order ; 1 ) ) to filter for all records that are the same year regardless of same month or not.
Thanks Phil! I can always count on you when I've reached my frustration point. The only thing I changed was the filtering. I used your formula, but since I applied it to filter the portal itself, I changed it to:
not IsEmpty(FilterValues(Expenses::Status Order; Dashboard::Status Filter)), which works perfectly no matter which button is chosen. Thanks again!
OK Phil - I have a new question but it's based off this portal, so I thought it would be easier to post it here then begin a new post. If you have time to help out, I'd appreciate it. If not, I will start a new post if that's easier. Thanks!
I created a pie chart (which is completely new to me) and basically want the data that the portal filters when Current Month, Current Year or Last Year are clicked. I created a pie chart Title: "Expenses", Category Labels: Expense Category::Name, Slice Data: Case(not IsEmpty(FilterValues(Expenses::Status Order;Dashboard::Status Filter));Expenses::Total) *Expenses::Total is a summary field that totals up Expenses::Amount. Then in Data Source I chose 'Related Records' for chart data and Related Table:Expenses. I selected "Summarized groups of records".
This partially works. When I click on 'Current Month' it only gives me one value at 100%. Clicking on Current Year and also clicking on Last Year does return the pie chart in working order, with the totals for each expense category showing up with the correct percentage. I'm stumped...
Update: I was playing around, adding in some more test records, and now I realize that it only seems like the pie chart was filtering correctly according to respective buttons. I now find that some categories will add together, and then others will show each record. And it seems to have something to do with the Expenses::Amount field - When I would click on the Current Month button, expenses would total and show on the pie chart, unless it was an expense named Home Office for $110. If I changed the amount, it would show up. Weird. Then when I would click on the Current Year, the Home Office items that were $110, would add together and the any Home Office items with a different total would show up as a new pie wedge. Clearly I've confused something besides myself...
The method that I recommended was for a filtered portal, not a chart. Charting, like other calculations references the data directly in the related table, by passing any defined portal filter. So an unfiltered portal based on your current relationship will show the data being used in your chart.