Dynamic exression for a Measure greater than current month

[Ravisha]

Hi All,

We do the cube reporting which is a DMR. Recently I got a requirement to show Actual Shipments(Measure column in Crosstab) only greater than current month through December.  meaning that it should be for the remaining months of the period so, current month through dec 2014 and should be dynamic ... Is it possible to achieve the same using dimensional functions ? I sincerely  request anyone to please guide me ..

I have two Time dimensions for Calendar year... one of them has "Current month" Hierarchy in it. and in other dimension I have Calendar month level having members like Jan, feb , march ... Dec.

Thank you all for your support

[MFGF]

Hi All,

We do the cube reporting which is a DMR. Recently I got a requirement to show Actual Shipments(Measure column in Crosstab) only greater than current month through December.  meaning that it should be for the remaining months of the period so, current month through dec 2014 and should be dynamic ... Is it possible to achieve the same using dimensional functions ? I sincerely  request anyone to please guide me ..

I have two Time dimensions for Calendar year... one of them has "Current month" Hierarchy in it. and in other dimension I have Calendar month level having members like Jan, feb , march ... Dec.

Thank you all for your support

Hi,

Is the Current Month based on today's date, or is it specifically the "Current Month" member from your package?

If it's today's date, you could perhaps do this with a macro, but we'd need to know the structure of your month MUNs to come up with an appropriate expression.

Can you right-click on a Month member in your package tree and go into Properties for the member, then copy and post up the Member Unique Name of the member?

Cheers!

MF.

[Ravisha]

Hello Sir,

Thanks for your reply. Yes, current month is based on current date.

for example: current month is April (04). So, I want to show my Actual shipments greater than April through December of this year. The logic should be dynamic.

MUN of April: [Affinity Sales Reports v3].[STANDARD CALENDAR].[CALENDAR MONTH].[CALENDAR MONTH1]->:[PC].[@MEMBER].[04]

I have also attached an image that shows the members of the Calendar month level in CY time dimension.

Thanks for your support sir

[MFGF]

Ah. It looks like your months don't sit within a year? Are your year members in a separate dimension?

MF.

[Ravisha]

Yes sir I have the years in separate CY time dimension ...I have attached the image of that CY time dimension

I am attaching the XML documents of my report and also an report output image which explains the requirement ... I would appreciate If you can please have a look at it ...

Thank you sir !

[Ravisha]

CY Time dimension Image

[Ravisha]

Report image output

[MFGF]

CY Time dimension Image

Oh. Here you have Year, quarter and month levels in the hierarchy. Is the MUN you posted a MUN from one of these month members?

MF.

[Ravisha]

No sir ... we actually have two time dimensions. CY Time dimension ( real time dimension) and CY dimension ( manual time dimension).The MUN which I gave is from the manual CY Dimension which doesn't have year, quarter and months in the hierarchy.

Do you want me to give you the current month member's (2014/Apr) MUN in Current month hierarchy of CY Time Dimension or just  the MUN of the month member 2014/Apr ?

1) MUN of the member from Current month hierarchy is below (real time dimension)

[Affinity Sales Reports v3].[Calendar].[Current Month].[Calendar Month]->:[PC].[@MEMBER].[20140401-20140430]

2) MUN of the  member from Calendar Month (real time dimension)
[Affinity Sales Reports v3].[Calendar].[Calendar].[Calendar Month]->:[PC].[@MEMBER].[20140401-20140430]

[MFGF]

No sir ... we actually have two time dimensions. CY Time dimension ( real time dimension) and CY dimension ( manual time dimension).The MUN which I gave is from the manual CY Dimension which doesn't have year, quarter and months in the hierarchy.

Do you want me to give you the current month member's (2014/Apr) MUN in Current month hierarchy of CY Time Dimension or just  the MUN of the month member 2014/Apr ?

1) MUN of the member from Current month hierarchy is below (real time dimension)

[Affinity Sales Reports v3].[Calendar].[Current Month].[Calendar Month]->:[PC].[@MEMBER].[20140401-20140430]

2) MUN of the  member from Calendar Month (real time dimension)
[Affinity Sales Reports v3].[Calendar].[Calendar].[Calendar Month]->:[PC].[@MEMBER].[20140401-20140430]

Thanks! Does it matter which time dimension you use in the report? I think it would be easier to use the real time dimension and the Calendar hierarchy which has Year, Quarter and Month levels, if this is acceptable?

So, you can get your current month with the following query calculation (let's call it [CurrMth]):

#'[Affinity Sales Reports v3].[Calendar].[Calendar].[Calendar Month]->:[PC].[@MEMBER].['+timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#

You can then use this in a periodsToDate() function based on your Year level - to get all months in the year up to and including this month (let's call it [PastMths]):

periodsToDate([Affinity Sales Reports v3].[Calendar].[Current Month].[Calendar Year],[CurrMth])

You can get the last month of the current year using a similar macro to the current month and the closingPeriod function - let's call this calculation [EndMth]:

closingPeriod([Affinity Sales Reports v3].[Calendar].[Calendar].[Calendar Month],#'[Affinity Sales Reports v3].[Calendar].[Calendar].[Calendar Year]->:[PC].[@MEMBER].['+timestampMask($current_timestamp,'yyyy')+'0101-'+timestampMask($current_timestamp,'yyyy')+'1231]'#)

Next we can use the periodsToDate with this to get all months in the current year - let's call this calc [AllMths]

periodsToDate([Affinity Sales Reports v3].[Calendar].[Current Month].[Calendar Year],[EndMth])

Finally we can use the except() function to get all months in [AllMths] that are not also in [PastMths], and we can aggregate your measure for all members in this set:

aggregate(currentMeasure within set except([AllMths],[PastMths]))

This should give you the aggregated measure values for all months after the current month, up until the end of the current year.

Cheers!

MF.

[Ravisha]

Hello Sir,

Absolutely Astounding ! I have no words to appreciate the efforts you've put into solve this issue...

I have to also add another column in the Cross tab which should show Shipments through the last complete month of shipments( less than current month). An example for CY 2014: we would want the shipments to be through the last complete month (i.e. Jan-Mar 2014). :o ... How can we achieve that sir

Once again, I appreciate your help and support..

[MFGF]

Hello Sir,

Absolutely Astounding ! I have no words to appreciate the efforts you've put into solve this issue...

I have to also add another column in the Cross tab which should show Shipments through the last complete month of shipments( less than current month). An example for CY 2014: we would want the shipments to be through the last complete month (i.e. Jan-Mar 2014). :o ... How can we achieve that sir

Once again, I appreciate your help and support..

You already have most of the pieces for this. Let's see what we already know:

1. We already know that we can get the current month member with this macro expression: #'[Affinity Sales Reports v3].[Calendar].[Calendar].[Calendar Month]->:[PC].[@MEMBER].['+timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#

2. We already know that we can specify a range of members from the beginning of the year using a periodsToDate() function, so all months this year up to and including the current month would be: periodsToDate([Affinity Sales Reports v3].[Calendar].[Current Month].[Calendar Year],[CurrMth])

3. We already know that we can obtain an aggregated measure value for a range of members using an aggregate() summary: aggregate(currentMeasure within set except([AllMths],[PastMths]))

So - what don't we already have that we need for this challenge? Well, if we have the current month, how do we get hold of the preceding month? The answer lies in either the prevMember() function or, if you prefer, the lag() function. Take a look at the tips for these in the expression editor for a calculation - go to the Functions tab, then expand Dimensional Functions and take a look at each of these.

The other key point is that we can use the aggregate() summary to aggregate any specified measure - not just currentMeasure. I think that's all we need for this.

The approach, then is to find the preceding month member to the current month, use the periodsToDate() function to get all months this year up to and including this member, and aggregate your Shipments measure within this set of members.

Rather than me writing the steps, why not have a go yourself using what we already know. If you get it working, maybe you could post up the solution for others to benefit from? If you get stuck, post back and tell us what you're struggling with and we will be happy to help :)

Cheers!

MF.

[Ravisha]

Hello Sir,

I apologize for the inconvenience caused to you... Like you said, I'll definitely look into the functions and I'll try to get a good hold of it. With that we have in our hand, I tried to get the shipments less than the current month .. but couldn't succeed in it . I didn't know about prevMember() function. I will try it out now

Thanks for you patience and support sir. I really appreciate it.

[MFGF]

Hello Sir,

I apologize for the inconvenience caused to you... Like you said, I'll definitely look into the functions and I'll try to get a good hold of it. With that we have in our hand, I tried to get the shipments less than the current month .. but couldn't succeed in it . I didn't know about prevMember() function. I will try it out now

Thanks for you patience and support sir. I really appreciate it.

Good luck! Hopefully you will find it a simple exercise to put these pieces together :)

MF.