Hi Team,
I have to find out default value of last month of latest quarter, which contains value, which i have done below
tail(lastChild(item(tail(filter(members([Fact KPI Summary Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Quarter]),tuple([Disbursement Amount],currentMember([Fact KPI Summary Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])) is not null),1),0)),1)
Now second, last month value of previous of latest quarter. I am not able to find out the second one.
The above values will display on default run of the report. if user selects any other quarter, then that selected quarter last month and its previous quarter last month value will display.
Please suggest!!!
Regards,
Inam
Quote from: inu on 31 Jan 2017 05:48:30 AM
Hi Team,
I have to find out default value of last month of latest quarter, which contains value, which i have done below
tail(lastChild(item(tail(filter(members([Fact KPI Summary Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Quarter]),tuple([Disbursement Amount],currentMember([Fact KPI Summary Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])) is not null),1),0)),1)
Now second, last month value of previous of latest quarter. I am not able to find out the second one.
The above values will display on default run of the report. if user selects any other quarter, then that selected quarter last month and its previous quarter last month value will display.
Please suggest!!!
Regards,
Inam
Can you clarify? If the report ran today and the last month of the current quarter returned as January 2017, which other month would you require? The last month of the prior quarter, or the corresponding month of the prior quarter (eg would you want to see December 2016 or would you want to see October 2016)?
MF.
Quote from: MFGF on 31 Jan 2017 06:11:35 AM
Can you clarify? If the report ran today and the last month of the current quarter returned as January 2017, which other month would you require? The last month of the prior quarter, or the corresponding month of the prior quarter (eg would you want to see December 2016 or would you want to see October 2016)?
Quote from: MFGF on 31 Jan 2017 06:11:35 AM
January 2017 never should come. Rather March 2016 will come ,due to last month of Quarter-4
and its previous quarter last month i-e December- 2016
Quote from: inu on 31 Jan 2017 08:48:19 AM
Yes, I explain,
Quarter-1(Apr, May, Jun)
Quarter-2(Jul, Aug, Sep)
Quarter-3(Oct, Nov, Dec)
Quarter-4(Jan, Feb, Mar)
Default Scenario(Latest quarter which has data available):
Suppose Quarter-3 has data, from this quarter Dec data should come into the report,
Hi,
I don't follow. How is Dec in Q3? Are your quarters different from the normal calendar year?
Quote from: inu on 31 Jan 2017 08:48:19 AM
I got this from below statement. Now its previous quarter i-e Sep data of Quarter-2 will display. which i am not able to found.
Yes, you got the point correctly. (The last month of the prior quarter).
You didn't answer my original question though. What if the latest month for which you have data is Nov rather than Dec? You'd want to see Nov and which other month - Sep or Aug? Is it always the last month of the prior quarter (Sep) or is it the equivalent month of the prior quarter (Aug)?
Quote from: inu on 31 Jan 2017 08:48:19 AM
Selected quarter from value prompt by user Scenario:
If user selects Quarter-2 , then Sep data with its respective positioned month i-e Jun for prior quarter will be used for calculation.
Note: Simply, we can understand the last month of selected quarter and last month of prior quarter.
Please suggest.
Thanks.
Can you explain clearly what the requirement is? Sounds like always the last month in the quarter (so Nov and Sep)?
MF.
Quote from: MFGF on 31 Jan 2017 10:39:07 AM
Hi,
I don't follow. How is Dec in Q3? Are your quarters different from the normal calendar year?
Quote from: MFGF on 31 Jan 2017 10:39:07 AM
This is financial year. Starts from April.
Quote from: MFGF on 31 Jan 2017 10:39:07 AM
Hi,
You didn't answer my original question though. What if the latest month for which you have data is Nov rather than Dec? You'd want to see Nov and which other month - Sep or Aug? Is it always the last month of the prior quarter (Sep) or is it the equivalent month of the prior quarter (Aug)?
Can you explain clearly what the requirement is? Sounds like always the last month in the quarter (so Nov and Sep)?
MF.
I explained MF. clearly by putting all quarter with repective months.
I said Dec, not Nov and why Aug you are considering. Yes , it will always the last month of prior quarter.
Quarter-1(Apr, May, Jun)
Quarter-2(Jul, Aug, Sep)
Quarter-3(Oct, Nov, Dec)
Quarter-4(Jan, Feb, Mar)
Thanks.
Inam
Quote from: inu on 01 Feb 2017 12:30:59 AM
I explained MF. clearly by putting all quarter with repective months.
I said Dec, not Nov and why Aug you are considering. Yes , it will always the last month of prior quarter.
Quarter-1(Apr, May, Jun)
Quarter-2(Jul, Aug, Sep)
Quarter-3(Oct, Nov, Dec)
Quarter-4(Jan, Feb, Mar)
Thanks.
Inam
In that case, either use a lag() function to obtain a month member 3 before the month you have, or use a parallelPeriod() function, or even a cousin() function.
Remember a few weeks ago BigChris suggested the following to you:
Quote from: BigChris on 19 Jan 2017 08:12:54 AM
Take a look at the Dimensional Functions in report studio - you should be able to find something in there...
The post is here, if you've lost it:
http://www.cognoise.com/index.php/topic,32268.msg105528.html#msg105528
If you had followed his advice here and looked through the functions to see what they do, you wouldn't even have had to post this question...
MF.
Quote from: MFGF on 01 Feb 2017 10:52:53 AM
In that case, either use a lag() function to obtain a month member 3 before the month you have, or use a parallelPeriod() function, or even a cousin() function.
Remember a few weeks ago BigChris suggested the following to you:
The post is here, if you've lost it:
http://www.cognoise.com/index.php/topic,32268.msg105528.html#msg105528
If you had followed his advice here and looked through the functions to see what they do, you wouldn't even have had to post this question...
MF.
Quote from: MFGF on 01 Feb 2017 10:52:53 AM
thanx MF. it resolved
lastChild(prevMember(item(tail (filter(members([Fact KPI Summary Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Quarter]),tuple([Disbursement Amount],currentMember([Fact KPI Summary Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])) is not null),1),0)))