How to get maximum of month key in dmr report

[inu]

Hi Team,
I want to get the maximum of month key in report.
Since ralational function doesnot support in dmr query.

Please suggest.


Regards,
Inam

[BigChris]

What have you tried so far and have you tried any Google searches (other search engines are available) to get to your answer?

I didn't know the answer to your question, but I've found it in much less than two minutes. If you can't find it then please post back and I'll happily share it with you

[bdbits]

Depending on how your dimensions are defined, probably head() or tail() on your month level will do the trick.

[inu]

What have you tried so far and have you tried any Google searches (other search engines are available) to get to your answer?

I didn't know the answer to your question, but I've found it in much less than two minutes. If you can't find it then please post back and I'll happily share it with you

Hi BigChris,
Path - [Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Month]->[all].[2015].[20154].[201511]
Ref  - [Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Month]->[all].[2015].[20154].[201511]
Member Caption - Feb-16
Member Unique Name - [Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Month]->[all].[2015].[20154].[201511]
Level Unique Name - [Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Month]
Hierarchy Unique Name - [Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date]
Dimension Unique Name - [Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)]
Level Number - 3
Parent Unique Name - [Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Quarter]->[all].[2015].[20154]

I got the above statement from properties of member.
I searched in google, spent 2-3 hours to resolve the issue.
Requirement is , i have to display disbursement amount for  latest month, and calculation in such a way that, (t-(t-1))/t (t=latest or current month)
that's why i wanted to get the maximum month value.
Apart from this, i am new to dmr

Regards,
Inam

[BigChris]

Have you tried using the Tail function? It looks to me like it should give you the data you're after:

tail ( set_expression [ , index_expression ] )
Returns the last "index_expression" elements of "set expression". The default for "index_expression" is 1.

Example: tail (members ( [great_outdoors_company].[Products].[Products].[Product line] ) )
Result: Returns the last member of the Product line set.
Personal Accessories

Example: tail ( members ( [great_outdoors_company].[Products].[Products].[Product line] ) , 2 )
Result: Returns the last two members of the Product line set.
Outdoor Protection
Personal Accessories

Take a look through the Dimensional Functions in Report Studio...there's all sorts of things that you can do with them.

[inu]

Have you tried using the Tail function? It looks to me like it should give you the data you're after:
Take a look through the Dimensional Functions in Report Studio...there's all sorts of things that you can do with them.

Thanx, for your reply.
I am getting the value perfectly.
Actually my requirement is I have to calculate the percentage of disbursement amount in such a way that (Disb_Amount of current month - Disb_Amount of previous month)/  Disb_Amount of current month.
current month means latest month in which value is available.

Please help, i got stuck into this since yesterday.

Regards,
Inam

[MFGF]

Thanx, for your reply.
I am getting the value perfectly.
Actually my requirement is I have to calculate the percentage of disbursement amount in such a way that (Disb_Amount of current month - Disb_Amount of previous month)/  Disb_Amount of current month.
current month means latest month in which value is available.

Please help, i got stuck into this since yesterday.

Regards,
Inam

Ok - let's recap where you are up to:
1. You have been pointed to the Tail() function to get you to the current month. You told us this works perfectly.
2. You appear to have a measure called [Disb_Amount]
3. You want to take the [Disb_Amount] measure value for the current month and the prior month for use in a calculation

So what are you missing to complete step 3?
Firstly, you need to figure out how to get a measure value / month member intersection (eg Disb_amount for the current month). BigChris suggested you look through the Dimensional Functions list in Report Studio - have you done this? I'll point you in the right direction - the function you need starts with T and rhymes with 'quadruple'
Secondly, you need to figure out how to get the previous month member to the current month. Again, look through the functions to see which one is appropriate. Probably the most obvious one starts with P and you can use it to get the prev member from your current month member. There might be a clue in the name.
Once you have these two important pieces, you then have all the tools you need to create an expression for 3 above.

I could just give you the expression, but where I've done that previously you don't seem to have taken the time to study it to understand what it does. Doing things this way, you're learning and improving your skills. :)

Your challenge, inu, is to post back the finished, working expression in this thread to help others in a similar situation. Are you up to the task?

Cheers!

MF.

[inu]

Ok - let's recap where you are up to:
1. You have been pointed to the Tail() function to get you to the current month. You told us this works perfectly.
2. You appear to have a measure called [Disb_Amount]
3. You want to take the [Disb_Amount] measure value for the current month and the prior month for use in a calculation

So what are you missing to complete step 3?
Firstly, you need to figure out how to get a measure value / month member intersection (eg Disb_amount for the current month). BigChris suggested you look through the Dimensional Functions list in Report Studio - have you done this? I'll point you in the right direction - the function you need starts with T and rhymes with 'quadruple'
Secondly, you need to figure out how to get the previous month member to the current month. Again, look through the functions to see which one is appropriate. Probably the most obvious one starts with P and you can use it to get the prev member from your current month member. There might be a clue in the name.
Once you have these two important pieces, you then have all the tools you need to create an expression for 3 above.

I could just give you the expression, but where I've done that previously you don't seem to have taken the time to study it to understand what it does. Doing things this way, you're learning and improving your skills. :)

Your challenge, inu, is to post back the finished, working expression in this thread to help others in a similar situation. Are you up to the task?

Cheers!

MF.

HI MF.
I was looking for your response,
what i did
1. Disb amount for current month (working and giving data)
(tuple([Disbursed Amount],currentMember([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])))

2. Disb amount for prev month (not giving data i-e blank)
(tuple([Disbursed Amount],prevMember(currentMember([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date]))))

I dont understand why it is blank.
and MF. i understood the use of these functions.thanx a lot.

Please suggest the final steps.

Regards,
Inam

[MFGF]

HI MF.
I was looking for your response,
what i did
1. Disb amount for current month (working and giving data)
(tuple([Disbursed Amount],currentMember([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])))

2. Disb amount for prev month (not giving data i-e blank)
(tuple([Disbursed Amount],prevMember(currentMember([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date]))))

I dont understand why it is blank.
and MF. i understood the use of these functions.thanx a lot.

Please suggest the final steps.

Regards,
Inam

Hi,

You're getting closer. The issue now is that you have misunderstood what the currentMember() function does. Let me help you out.

When you create a dimensional report (eg a crosstab), you bring in sets of members for the rows and the columns. The rows might be Product members (eg A, B, C) and the columns might be Year members (eg 2014, 2015, 2016). Each set of members is read one-member-at-a-time in an iteration as the crosstab is assembled. So in the rows Product A is read first, then product B, then Product C. If you wanted to get hold of the current member of the iteration in a calculation, you would use the currentMember() function. This means as the second row is being assembled, Product B would be returned by this function. Get the idea?

Now, does this give you the latest (ie last) member in the set every time? Hopefully you can see the answer is no. This means the currentMember function isn't the correct function to return the latest/last/newest month in your situation. In fact, you have already been given the Tail() function as one that can return the last member. Is there a reason you're not using this? An alternative would be the closingPeriod() function. You could use either.

If we substitute this into your expression, what do we get?

1. Disb amount for current month
tuple(closingPeriod([your Months level]), [Disbursed Amount])

2. Disb amount for prev month
tuple(prevMember(closingPeriod([your Months level])), [Disbursed Amount])

Oh. Looks like I did most of the work for you. All you need to do now is to put together the finished expression.

MF.

[inu]

Hi,

You're getting closer. The issue now is that you have misunderstood what the currentMember() function does. Let me help you out.

When you create a dimensional report (eg a crosstab), you bring in sets of members for the rows and the columns. The rows might be Product members (eg A, B, C) and the columns might be Year members (eg 2014, 2015, 2016). Each set of members is read one-member-at-a-time in an iteration as the crosstab is assembled. So in the rows Product A is read first, then product B, then Product C. If you wanted to get hold of the current member of the iteration in a calculation, you would use the currentMember() function. This means as the second row is being assembled, Product B would be returned by this function. Get the idea?

Now, does this give you the latest (ie last) member in the set every time? Hopefully you can see the answer is no. This means the currentMember function isn't the correct function to return the latest/last/newest month in your situation. In fact, you have already been given the Tail() function as one that can return the last member. Is there a reason you're not using this? An alternative would be the closingPeriod() function. You could use either.

If we substitute this into your expression, what do we get?

1. Disb amount for current month
tuple(closingPeriod([your Months level]), [Disbursed Amount])

2. Disb amount for prev month
tuple(prevMember(closingPeriod([your Months level])), [Disbursed Amount])

Oh. Looks like I did most of the work for you. All you need to do now is to put together the finished expression.

MF.

Thanx MF. You are spending time to make me understand the concept.
When I use above code, i get Jan-17 as curr month , but this month has no data, i want the latest month which has data.
So What i did
1. prevMember(item(tail (filter(members([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Month]),tuple([Disbursed Amount],currentMember([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])) is not null),1),0))

2. item(tail (filter(members([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Month]),tuple([Disbursed Amount],currentMember([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])) is not null),1),0)

3. ((tuple ([Disbursed Amount],[cur mon]) - tuple ([Disbursed Amount],[prev mon])) / tuple ([Disbursed Amount],[cur mon])) * 100

Now in this step i really need your help. when i use this in crosstab report without detail filter(Month key=201608) , it works fine, all three data items gives value perfectly,  but with detail filter(Month key=201608) crosstab displays only current month data item value.

I would like to tell you that current month value means latest month which has value in DB is 201608(Nov-16). if i change the filter value as month key=201607, crosstab displays data only for 201707.

I think you got the point. i am missing something in the code itself. Please rectify.

Thanks a lot.



Regards,
Inam

[MFGF]

Thanx MF. You are spending time to make me understand the concept.
When I use above code, i get Jan-17 as curr month , but this month has no data, i want the latest month which has data.
So What i did
1. prevMember(item(tail (filter(members([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Month]),tuple([Disbursed Amount],currentMember([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])) is not null),1),0))

2. item(tail (filter(members([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date].[Month]),tuple([Disbursed Amount],currentMember([Agreement Fact - Monthly (Analysis)].[Time Dimension(Fiscal)].[Date])) is not null),1),0)

3. ((tuple ([Disbursed Amount],[cur mon]) - tuple ([Disbursed Amount],[prev mon])) / tuple ([Disbursed Amount],[cur mon])) * 100

Now in this step i really need your help. when i use this in crosstab report without detail filter(Month key=201608) , it works fine, all three data items gives value perfectly,  but with detail filter(Month key=201608) crosstab displays only current month data item value.

I would like to tell you that current month value means latest month which has value in DB is 201608(Nov-16). if i change the filter value as month key=201607, crosstab displays data only for 201707.

I think you got the point. i am missing something in the code itself. Please rectify.

Thanks a lot.



Regards,
Inam

Hi,

Ok - more progress. Your last challenge is filtering. Using detail filters in dimensional reports is a BIG red flag. Don't do it - you'll get incorrect, inconsistent results. Take a look at FAQ #4 in the FAQs post to see why: http://www.cognoise.com/index.php/topic,27563.0.html

The alternative and approved method is to use dimensional expressions to focus on the members you require. It's not clear here how your report should work, though.

Can you explain what you want to achieve with the month filter? Without a filter you will get the latest and prior month in your calculation. What should happen if you select a different month? Should you still get the latest and prior month? Should you get different months? Can you explain the requirement?

Cheers!

MF.

[inu]

Hi,

Ok - more progress. Your last challenge is filtering. Using detail filters in dimensional reports is a BIG red flag. Don't do it - you'll get incorrect, inconsistent results. Take a look at FAQ #4 in the FAQs post to see why: http://www.cognoise.com/index.php/topic,27563.0.html

The alternative and approved method is to use dimensional expressions to focus on the members you require. It's not clear here how your report should work, though.

Can you explain what you want to achieve with the month filter? Without a filter you will get the latest and prior month in your calculation. What should happen if you select a different month? Should you still get the latest and prior month? Should you get different months? Can you explain the requirement?

Cheers!

MF.

Sure, I will explain.
i have a date(Month Name(i-e Nov-16) ), product type, loan type value prompts, all are single select prompts. When the dashboard will be run by user, data will show based on current month(latest month which has data in DB); on further if user selects any month say Oct-16 / Sep-16 so on, data will show according to the selected month.
For example, by default data will display for Nov-16, and on calculated report item, value will show on Nov-16 and its previous month i-e Oct-16. If user selects other month say Sep-16, then on calculated report item, value will show on Sep-16 and its previous month i-e Aug-16. We dont need to show previous month data, for calculation only we need previous month data.

In my dashboard one bubble chart is there where i have to put the Disbursed Amount %(for which i am struggling now), and other KPIs.
Apart from this three more charts are there, they are not more challenges.

Please suggest, how can i achieve the target. my manager asking me repeatedly about the issue fixed or not.
Hence, for  the time being i have to calculate the percentage of [Disbursed Amount]

If still not clear, Can we discuss on call (8884804981)

See the attached file.


Regards,

[inu]

Sure, I will explain.
i have a date(Month Name(i-e Nov-16) ), product type, loan type value prompts, all are single select prompts. When the dashboard will be run by user, data will show based on current month(latest month which has data in DB); on further if user selects any month say Oct-16 / Sep-16 so on, data will show according to the selected month.
For example, by default data will display for Nov-16, and on calculated report item, value will show on Nov-16 and its previous month i-e Oct-16. If user selects other month say Sep-16, then on calculated report item, value will show on Sep-16 and its previous month i-e Aug-16. We dont need to show previous month data, for calculation only we need previous month data.

In my dashboard one bubble chart is there where i have to put the Disbursed Amount %(for which i am struggling now), and other KPIs.
Apart from this three more charts are there, they are not more challenges.

Please suggest, how can i achieve the target. my manager asking me repeatedly about the issue fixed or not.
Hence, for  the time being i have to calculate the percentage of [Disbursed Amount]

If still not clear, Can we discuss on call (8884804981)

See the attached file.


Regards,

[inu]

Sure, I will explain.
i have a date(Month Name(i-e Nov-16) ), product type, loan type value prompts, all are single select prompts. When the dashboard will be run by user, data will show based on current month(latest month which has data in DB); on further if user selects any month say Oct-16 / Sep-16 so on, data will show according to the selected month.
For example, by default data will display for Nov-16, and on calculated report item, value will show on Nov-16 and its previous month i-e Oct-16. If user selects other month say Sep-16, then on calculated report item, value will show on Sep-16 and its previous month i-e Aug-16. We dont need to show previous month data, for calculation only we need previous month data.

In my dashboard one bubble chart is there where i have to put the Disbursed Amount %(for which i am struggling now), and other KPIs.
Apart from this three more charts are there, they are not more challenges.

Please suggest, how can i achieve the target. my manager asking me repeatedly about the issue fixed or not.
Hence, for  the time being i have to calculate the percentage of [Disbursed Amount]

If still not clear, Can we discuss on call (8884804981)

See the attached file.


Regards,

[MFGF]

Sure, I will explain.
i have a date(Month Name(i-e Nov-16) ), product type, loan type value prompts, all are single select prompts. When the dashboard will be run by user, data will show based on current month(latest month which has data in DB); on further if user selects any month say Oct-16 / Sep-16 so on, data will show according to the selected month.
For example, by default data will display for Nov-16, and on calculated report item, value will show on Nov-16 and its previous month i-e Oct-16. If user selects other month say Sep-16, then on calculated report item, value will show on Sep-16 and its previous month i-e Aug-16. We dont need to show previous month data, for calculation only we need previous month data.

In my dashboard one bubble chart is there where i have to put the Disbursed Amount %(for which i am struggling now), and other KPIs.
Apart from this three more charts are there, they are not more challenges.

Please suggest, how can i achieve the target. my manager asking me repeatedly about the issue fixed or not.
Hence, for  the time being i have to calculate the percentage of [Disbursed Amount]

If still not clear, Can we discuss on call (8884804981)

See the attached file.


Regards,

Hi,

This changes things massively. You never previously mentioned you were prompting for a month to be used in the calculation. Didn't you think it might be an important factor?

What I think you want is this...

To prompt for a month and to have your calculation take the prompted month, the prior month to it, and perform your calculation? If no month is selected you want to use the "current" month as the default, but the "current" month is determined as the latest month for which you are holding measure values. Is this the case?

If I'm right, this provides a really difficult challenge. The prompted for month and prior month alone are easy. The latest month for which you have measure values and the prior month are fairly easy (we already have this). The really difficult part is using the second scenario as the default for the first scenario. I can't immediately think of a way to do this, inu.

For the first part, you'd have a calculation for prompted month in your crosstab with the expression
[your month level] -> ?Your Month parameter?

The prior month to this would be
prevMember([your Month level] -> ?Your Month parameter?)

If you had a set default to use, you could either put the MUN of that member in as the default selection for the prompt, or else you could use a prompt() macro function with a default member coded. If the default was the month corresponding to the month in the current date, you could use a prompt macro with a default that created the MUN of the current month based on the current date (ie Jan 2017 for this month). Neither of those is your requirement, though, so I'm struggling to see how you can achieve this.

Oh, I'd remove your phone number from the forum to protect yourself too.

MF.