Set Analysis not working

[#1]

Hello Everyone,

I have this YTD formula working very well. _YYYY and _MM is calendar.

=sum ( {$<[Year] = {$(#=Only(_YYYY))}, [Month] = {"<=$(#=Only(_MM))"}>} [Rev-Actual])

When I do current month, it doesn't work:

=sum ( {$<[Year] = {$(#=Only(_YYYY))}, [Month] = {"$(#=Only(_MM))"}>} [Rev-Actual])

Can you help?

Thank you very much in advance.

[sunny_talwar]

Not sure, but what about this:

=Sum({$<[Year] = {$(#=Only(_YYYY))}, [Month] = {"=$(#=Only(_MM))"}>} [Rev-Actual])

[swuehl]

How do the Month and _MM field values look like?

If you put your expression in a straight table chart with no expression label, then hover with the mouse over the expression column header, what is displayed with dollar sign expansions performed?

[#4]

Thank you - Sunny,

Your formula can bring data in but cannot change when click on Month bar. It just show one month data and not change.

Do you have a second thoughts?

Appreciate your help.

[#5]

swuehl,

_MM is 1,2, 3...12.

This is a calendar and I have month bar on my app. I want to see different month revenue by click on month.

Thanks.

[swuehl]

Ok, so I assume Month field  values are also 1,2,3,...12 and the expanded expression looks like

=sum ( {$<[Year] = {2016}, [Month] = {"3"}>} [Rev-Actual])

Right?

edit:

If so, could you detail what is not working? And have you checked that you actually have data for that current month?

[#7]

swuehl,

I checked on Month is 'Jan' three letter but I have PdNo is number for each month.

sum ( {$<[Year] = {$(#=Only(_YYYY))}, [PdNo] = {'$(#=Only(_MM))'}>} [Rev-Actual])

This formula is not working too.

Thanks.

[sunny_talwar]

What if you do this:

Sum({$<[Year] = {$(#=Only(_YYYY))}, [PdNo] = {'$(#=Only(PdNo))'}>} [Rev-Actual])

[#9]

Sunny,

This one can not bring any value but 0.

Thank you very much.

[swuehl · Accepted solution]

Please

a) describe what you mean with 'not working': Computer crashes, expression returns zero, expression returns <> zero but other than expected (then also describe how you got your expected results)

b) check that =Only(_MM) returns a value that matches a value from PdNo.

Try making selections in Year and PdNo manually and check the result of sum([Rev-Actual])

c) Check that you haven't made other selections, potentially interfering with your set analysis

It would make things much easier if you could post a small sample QVW that demonstrates your issue