Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Not sure, but what about this:
=Sum({$<[Year] = {$(#=Only(_YYYY))}, [Month] = {"=$(#=Only(_MM))"}>} [Rev-Actual])
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?
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.
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.
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?
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.
What if you do this:
Sum({$<[Year] = {$(#=Only(_YYYY))}, [PdNo] = {'$(#=Only(PdNo))'}>} [Rev-Actual])
Sunny,
This one can not bring any value but 0.
Thank you very much.
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