EXCEL: Only show Data from the Previous month (Sort Descending-no zeros)

Hmm really not sure what's going on as it works fine when I test taking months off to check it's dynamic. Is this any better?

Filter updated.yxmd

All comments

kathleenmonks

Hi @neromerob

See my workflow attached. The steps I took:

Transpose the months
Separate out the month and year
Compare the month and year to the previous month and year (accounting for Jan ->Dec)
Filter data that is not from previous month
Crosstab back to original format
Filter out zero values
Sort Descending

last_month.yxzp

Hi @kathleenmonks

I saw the workflow but there something i don´t understand.

for what I see, may is fixed to be previous month, meaning that when June becomes the Previous month i will have to change it manually, not quite sure how to do that dynamic.

Thank you again.

kathleenmonks

@neromerob Good catch! We can just move the filter and sort to before the crosstab. See attached.

last_month.yxzp

@neromerob One way of doing this with the dynamic select tool, the sorting part also dynamic

Dynamic_Select_30_06.yxmd

Most recent prev month with no 0 sorted desc.yxmd

Not sure if I've maybe misunderstood but this shows only the most recent month where there are no 0 values and then sorts that month descending. From the wording I couldn't work out whether you meant that or keep the month even if all values are 0, in which case the others' solutions would work fine.

Hello @kathleenmonks

thank you for your effort.

I have see that now is dynamic but like in the workflow of @binu_acs the value of the month are not sort.

It´s like the sort before the summarize looses effect in the final result

Thank you for your help and time.

Hello @DataNath

I test it with the real data, it show me short descending, but it shows me January_2022 instead of May_2022

@neromerob Do you mean my workflow is not sorting your data?

hello @binu_acs

Let me show you the result

Are there months in your data where all rows are 0 @neromerob? If so, I think I misunderstood the ask as I filter these out to only show the most recent with non-0 values. I've just tested the flow a few times by taking off various final months and it always takes off the second most recent (that aren't full of 0s).

If you don't want to remove months if they're all 0 then this flow is an alternative.

Most recent prev month sorted desc.yxmd

Hello @DataNath

What i´m looking for is to get only the data from the previous month (in this case May) and showing only when the month is different from zero. And it should be dynamic for next "previous months"

So you only want rows that aren't 0? If so the flow is below.

If you mean when the whole field is 0, then my earlier solution does that. The reason it will have jumped back to January is that will be the most recent previous month without all 0s.

Most recent prev month sorted desc.yxmd

@neromerob I am not sure why you are getting different result. Can you check the attached workflow?

Dynamic_Select_30_06.yxzp

hello @binu_acs

Now it seem to be working (I mean the previous month, and sort descending) but is showing the zero values.

without the zeros would be ok.

Thank you for your time.

hello @DataNath

The result that you show is the one that I need, although the workflow that is attached still how me January.

Sorry for the inconvenience

Hmm really not sure what's going on as it works fine when I test taking months off to check it's dynamic. Is this any better?

Filter updated.yxmd

@neromerob updated the workflow by adding a filter tool to remove the zero values

Dynamic_Select_30_06.yxzp