Dynamic Filter Syntax Assistance

[shellp]

Hello,

I want to create filter formulae dynamically based on values in cells in the worksheet. The formula below works to filter [Fiscal Year] to the value in C4:

Filter(raw_All, Indirect("raw_All[Fiscal Year]")=""&C4)

But what if I want the equal sign (=) to also be dynamic i.e. in B4 where the = or <> would be present in the above formula - what is the syntax? What if I want [Fiscal Year] to be dynamic based on a column name in C5 and how does that impact the formula above?

Any and all assistance greatly appreciated.

 

[6StringJazzer]

If raw_All is a table, and Fiscal Year is a column in that table, you don't need INDIRECT.

If the table and C4 both contain numeric values you do not need to, and should not, append the null string for your comparison.

You cannot make an operator dynamic. However, you can do something like this. In B4 put something like EQUAL or NOT EQUAL then your formula can be

=FILTER(raw_All, (OR(AND(B4="EQUAL",raw_All[Fiscal Year]=C4),AND(B4="NOT EQUAL",raw_All[Fiscal Year]<>C4))))

If you want the column reference to be dynamic then you will need INDIRECT.

=FILTER(raw_All, (OR(AND(B4="EQUAL",INDIRECT("raw_All["&C5&"]")=C4),AND(B4="NOT EQUAL",INDIRECT("raw_All["&C5&"]")<>C4))))

However, I'm not sure what you're trying to do. This last formula still assumes you have a column of years. If you want to check for a match on year on Fiscal Year but return the data from a different column, you need a different formula. But the formula above gives you exactly what you asked for.

 

[shellp]

Hello,

Thank you so much for your response. I went with the following that works:

=FILTER(raw_All,(IF(C4="All",raw_All[Fiscal Year]<>"",raw_All[Fiscal Year]=C4))*(IF(C6="All",raw_All[Month]<>"",TEXT(raw_All[Month],"MMMM")=C6))*(IF(C8="All",raw_All[DOW]<>"",raw_All[DOW]=C8))*(IF(E4="TOH",raw_All[campus]<>"",raw_All[campus]=E4))*(IF(E6="All",raw_All[Gender]<>"",raw_All[Gender]=E6))*(IF(E8="All",raw_All[AgeGroup]<>"",raw_All[AgeGroup]=E8))*(IF(H4="All",raw_All[Marital_Status_Desc]<>"",raw_All[Marital_Status_Desc]=H4))*(IF(H6="All",raw_All[AdmFromDesc]<>"",raw_All[AdmFromDesc]=H6))*(IF(H8="All",raw_All[Disposition]<>"",raw_All[Disposition]=H8))*(raw_All[Fall_Visit]>0))

Note that the data is one line per data so how do I use this filter in a formula to summarize specific columns and summing raw_all[total_visits]? How to use a filter like this in a let statement to get various other fields? Thanks.

 

[6StringJazzer]

Your question just got a lot bigger. Someone else may be able to help but I am not able to solve this without being able to see the file.

 

[shellp]

Thanks Jeff. Should I post as a separate question?

 

[6StringJazzer]

I'm not sure what the convention is for that here, and I'm not a moderator. Anybody can still contribute to this thread.

The essential thing is, I think, that we need to see your table.

 

[shellp]

Hello Again,

Below is a picture of the worksheet the user will be able to select "all" or a specific drop down from the dataset.

The formula below accurately filters raw_all based on the filter selections above.

=FILTER(raw_All,(IF(C4="All",raw_All[Fiscal Year]<>"",raw_All[Fiscal Year]=C4))*(IF(C6="All",raw_All[Month]<>"",TEXT(raw_All[Month],"MMMM")=C6))*(IF(C8="All",raw_All[DOW]<>"",raw_All[DOW]=C8))*(IF(E4="TOH",raw_All[campus]<>"",raw_All[campus]=E4))*(IF(E6="All",raw_All[Gender]<>"",raw_All[Gender]=E6))*(IF(E8="All",raw_All[AgeGroup]<>"",raw_All[AgeGroup]=E8))*(IF(H4="All",raw_All[Marital_Status_Desc]<>"",raw_All[Marital_Status_Desc]=H4))*(IF(H6="All",raw_All[AdmFromDesc]<>"",raw_All[AdmFromDesc]=H6))*(IF(H8="All",raw_All[Disposition]<>"",raw_All[Disposition]=H8))*(raw_All[Fall_Visit]>0))

The Columns fields are also drop down and the number is the column ID so I can dynamically choose ChooseCols() (or whatever other syntax is required) for these fields. In other words, the filter fields don't necessarily become grouping fields. The "raw_all" table is one row per encounter and I want to sum the "count" field per the 3 columns above that are selected based on the filter formula selected. Note the "etc" is just so I am not indicating all 7 days of the week for demonstration purposes but would want each as applicable in the actual table.

I am assuming this can be done in a spill array but I just am not sure how to go about it. Any assistance greatly appreciated, thanks.

Shelley

 

[6StringJazzer]

But you still haven't shown your source data table.

 

[shellp]

Sorry, I thought you just wanted the resulting table.

raw_all.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Fiscal Year	Month	Gender	DOW	AgeGroup	Marital_Status_Desc	AdmFromDesc	Disposition	FallDxDesc	MainDx	Fall_Visit	Count	campus	MRN	Encounter	AmCareType	RegDate	FallDx	DOW_Num	Age	AgeGrp	Marital_Status	InstFrom	InstFromType	InstFromDesc
2	2013	Apr	F	Monday	75 to 84	UNKNOWN	Inpatient complex continuing care	Discharged Home (private dwelling, not an institution) With NO Support Services	Fall on same level from slipping, tripping and stumbling	Injury of nerves and spinal cord at neck level	1	1	Sunshine General	111111	2013000000	ED	2013-04-01	W01	2	75	3	U	52629	3	Almonte General Hospital
3	2013	Apr	M	Monday	75 to 84	MARRIED	No Institution - Admit from Home	Discharged Home (private dwelling, not an institution) With NO Support Services		Pneumonia, organism unspecified	0	1	Sunshine General	222222	2013111111	ED	2013-04-01		2	78	3	M
4	2013	Apr	M	Monday	65 to 74	UNKNOWN	No Institution - Admit from Home	Discharged Home (private dwelling, not an institution) With NO Support Services		Other functional intestinal disorders	0	1	Sunshine General	333333	2013222222	ED	2013-04-01		2	74	3	U
5	2013	Apr	M	Monday	65 to 74	SINGLE	Ambulatory care clinic	Admitted directly to ICU/SCU or OR room (in own facility)		Intracranial injury	0	1	Sunshine General	444444	2013333333	ED	2013-04-01		2	69	2	S	54071	N	Pembroke Regional Hospital (AM)
6	2013	Apr	F	Monday	85 and older	WIDOWED	No Institution - Admit from Home	Discharged Home (private dwelling, not an institution) With NO Support Services		Pain in throat and chest	0	1	Sunshine General	555555	2013444444	ED	2013-04-01		2	91	3	W
7	2013	Apr	F	Monday	65 to 74	WIDOWED	No Institution - Admit from Home	Discharged Home (private dwelling, not an institution) With NO Support Services		Malignant neoplasm of bronchus and lung	0	1	Sunshine General	666666	2013555555	ED	2013-04-01		2	68	2	W
Sheet1

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Spill Array Syntax
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.