Reply To: Working days and remaining days

#526
 Frank
Keymaster
  • Topics Started - 74
  • Replies Written - 52

I’m assuming you have populated the category and are able to see the working days by at least one level – probably by company. If this is not the case the rest of this post won’t help…. The problem comes when you want to look at the working days by another dim, as you mentioned – customer, rep or region, or some combo of those.

You’ll need to use the same technique that we use to see data like inventory that is that is not stored by a dim that you are drilled to – that example is here

I’ll paste the calc measure that I use to see the working days when I’m looking at a bunch of other dims below. This uses the idea of DefaultMember with a dimension in a tuple.

Here is the calc I’m using – you’ll need to substitute your dims and measure names. I have the working days stored only at the company level for company 100

_________________________________________________________________________________________________
/Make sure there is sales data for the row

Iif([Measures].[Data4 (Sales Amount Jan to Sep )]=0 AND [Measures].[Data3 (Plan Jan to Sep )] = 0 ,null,

//Work days are only available for company ? – so select those.
//work days are only at the Company level, so ignore other dims

([Company].[Company].[Company].[100],
[Region].[Region].DefaultMember,
[Customer SoldTo].[Customer SoldTo].DefaultMember,
[Distribution Channel].[Distribution Channel].DefaultMember,
[Division].[Division].DefaultMember,
[Product Category].[Product Category].DefaultMember,
[Product].[Product].DefaultMember,
[Salesperson].[Salesperson].DefaultMember,
[Product Type].[Product Type].DefaultMember,
[Product Family].[Product Family].DefaultMember,
[Product Brand].[Product Brand].DefaultMember,
[Measures].[Data10 (System Control Values Number of Days Sep 17 to Sep 17)]))

________________________________________________________________________________________

The Iif at the start is to make sure that there is data for a row, so the calc is not performed for null rows – this is needed to make sure you only see rows you want to see.

This calc allows me so see the working days for any of the dimensions listed that have the defualtmember in the member expression.

I’ve got a separate measure that is just the normal measure for the working days in Sept. – which is the Data10 measure used in the tuple.

It all this was meaningless or not helpful let me know and I’ll try again.

CONTACT US

We're not around right now. But you can send us an email and we'll get back to you, asap.

Sending

©2021 KLEO Template a premium and multipurpose theme from Seventh Queen

Log in with your credentials

or    

Forgot your details?