how to dynamically filter on a column that is created in a crosstab

I have a set of numeric columns that are created in a crosstab.

1. I need to dynamically identify the new column which has a title equal to the min value (which is a string variation of a date) of the possible column titles.

2. after identifying that column i need to filter out null values from that column (the easy part).

in this example the column i want to dynamically select for filtering is 2019M01.

i have fully flushed out the limitations of dynamic select with this use case.

Thanks for your help Community!

Preparation

Accepted answers

seven

Hi @davidhenington

before you Crosstab, you can Summarize to create a distinct list (Group By) of the fields which will be used as headers in the Crosstab. Then you can take that list, date parse the strings into actual dates, take the min of those dates, and solve your first need.

Why do you need to filter out nulls just from that column? That is just for my curiosity. For the solution to your second need, join the min value from step 1 to the pre-crosstab data, filter that dataset to remove nulls, then union back to the unjoined data and you're done.

It sounds like you're a more advanced user and what I wrote should get you there. If you're still struggling let me know and I'll mock up a workflow. (which I might still do anyway)

All comments

seven

Hi @davidhenington

It sounds like you're a more advanced user and what I wrote should get you there. If you're still struggling let me know and I'll mock up a workflow. (which I might still do anyway)

seven

Hi @davidhenington

that's the best I could without data, sample or otherwise. Images aren't terribly helpful unfortunately. Please let me know if this solves your case.

filter_crosstab.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703