Hi Experts,
we have different cube for each country. So we are using macro pull query item based upon the country parameter. But in parallelperiod function is working when we select county on prompt page for the first time. but after that if try to change country it gives below error
The model item '.[TIME (ALL)].[TIME (ALL)].[MONTH]' is invalid.
we are using the below code
parallelPeriod (#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].TIME (ALL)].[TIME (ALL)].[MONTH]')#,0,#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[MONTH]')# ->?p_Month?)
That snippet really confuses me. Why use it if you have a 0 in there? Are you using it elsewhere? Also, mixing macros and aliases like that is an interesting choice. What value are you passing to p_Month?
Can you paste in a full example of a mun?
we are trying to compare current year and month data with previous available data.
For example if we are passing 2017 and 06 , report should show data data for the 2017/05. if 2017/05 data is not available then it should show data for 2017/04 or any previous available data.
I suspect that the combination of prompt macros and alias is what's causing the error you're seeing. Try changing the alias to a macro and see what happens. Can you paste in an example of a mun?
parallelPeriod will give you a specific month, but it sounds like you really want only the last month with data. How about something like:
item(tail(filter(except(monthLevel,selectedMonth),measure is not null)),0)
That returns the last month with data as a single member.
Thanks a lot for the reply ,CognosPaul. I tried ur code . its giving me the previous data for example we have 2018-6,2018-3 and 2017-12 data. when i select month 2018-6 it gives me correct out put of 2018-3 but when i select 2018-3 it is not giving me 2017-12 data.
can you paste in the expressions for both items?
current data query :- parallelPeriod (#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].TIME (ALL)].[TIME (ALL)].[MONTH]')#,0,#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[MONTH]')# ->?p_Month?)
previous data query :parallelPeriod (#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].TIME (ALL)].[TIME (ALL)].[MONTH]')#,1#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[MONTH]')# ->?p_Month?)
Try this:
current data: #prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[MONTH]')# ->?p_Month?
previous data:
item(tail(filter(except(periodsToDate(#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[ALL LEVEL]')#),[current data]),measure is not null)),0)
periodsToDate to will take the top level of the time dimension, generating all of the members from the beginning of the cube to the selected member. Filter will limit that to only the members with data, tail returns the last one as a set, and item will extract the member from the set.
If that doesn't work then you need to check if there are any other members that the measure need to be tupled on. It may be possible that the filter isn't including items from the slicer.