Hi,
I have a data set currently set out similar to the below example (but with a lot more depth to the information). I have a number of dashboards which query the data in different ways and provide a number of views.
One of the views i had required me to pull out where i had duplicated Ref ID's but only in the cases where the status was valid (in this case all valid statuses contain " - ".
| Sys ID | Ref ID | Status | Name | Description |
|---|
| 1 | 12345 | 1 - plan | project a | project info |
| 2 | 54321 | 2 - build | project b | project info |
| 3 | 11112 | 2 - build | project c | project info |
| 4 | 12457 | rejected reason a | project d | project info |
| 5 | | 4 - concluded | project e | project info |
| 6 | 12345 | 2 - build | project f | project info |
| 7 | 12345 | funding denied | project g | project info |
I used the below logic to come up with a simple table view that works:
=IF(wildmatch([Status],'*-*'),IF([Ref ID]>0,IF(count([Ref ID])>1,count([Ref ID]),),))
which allows me to exclude both where there is not a valid status and where there are blank Ref IDs... IE using the example table i would expect to return results with a total at the bottom:
(this result is Sys ID 1 and 6)
As previously mentioned i added a number of other views to look at other requirements and decided the best way to look at things was a high level summary tab.
For this i have a text box with the condition for each table and then another text box with the total for that condition.
I am stuck though because i want to display the total from the results table (directly above) in this text box.
I have tried using another count/sum statement around my formula to get the result i.e.
=sum(IF(wildmatch([Status],'*-*'),IF([Ref ID]>0,IF(count([Ref ID])>1,count([Ref ID]),),)))
but this just does not work plain and simple.... can anyone give me some direction as how to solve this (i suspect it involves set analysis of some sort but i have quite a bit going on in the formula and can't get it clear in my head!)
