How to write a rule to asign not applicable (N/A) value?

[abcuser]

Hi,
using Cognos 10.1.1 fixpack 2 on Windows 2008 R2. I have created a cube in TM1 Architect. I have a dimension Measure type if I double click (Subset Editor opens) and click on Hiearchy Sort I get the following structure:

Code: Select all

Measure type
==> kilograms
==> square meters
==> number of items
==> ... (many more)

For top member Measure type the value is not reasonable - there should be no sum of children, because there would be apple and organge sum like problem. In Rules Editor I have written the following formula:

Code: Select all

['Measure type']=0;

but end-users complaned that 0 should not be used in this case but some "not applicable" like N/A should be used instead.

So I changed the code to:

Code: Select all

['Measure type']=0/0;

Because of matematical division by zero problem the result is #N/A. This works fine! Bellow is a simplified sample in Cognos BI - Cognos Viewer. It is clearly seen the difference between zero values or not/available AND not applicable.

cognos_viewer_not_applicable_sample.png (8.12 KiB) Viewed 15089 times

The code is working excellent. There is a small problem that I as a perfectionist don't like, this rule code is hard to read - specially by non-author of the code - because it it a trick, workaround. Is there any other way to set N/A value directly like for example: ['Measure type']=N/A or something similar, to make a code more readable?

Thanks a lot

[deepakjain2020]

Try below code

['Measure type']=S: 'N/A ';

Regards,
Deepak Jain

[tomok]

Try below code

['Measure type']=S: 'N/A ';

Regards,
Deepak Jain

No. In order for this to work Measure Type would have to be defined as S (string) and it is clear he has it as a C (consolidation). If he were to change it to S then it would not be the parent node and he would lose the defined hierarchy. You just need to learn to live with it. It is extremely minor and trust me, you are going to encounter a lot of similar type things if you continue with TM1. It goes with the territory.

[jim wood]

1) Why do you have a consolidation of your measures at all? Sounds like somebody is trying to replicate Essbase.
2) If you change / to \ you won't get NA

[abcuser]

First of all, thanks a lot for your replies and thoughts. I really appreciate.

@deepakjain2020, this formula does not work. I have a consolidation member and I don't want to lose hiearchy. Beside this problem, a year ago I have created some string member and Cognos BI only supports numbers as measure values, so members in TM1 member type has to be defined as N or C. So S type of member is not allowed in Cognos BI (at least it wasn't at the time I have tested, now some fixpacks were applied so haven't tested the latest and the greatest).

@tomok, I just thought there is better solution then just adding some dummy formula.

@jim wood,
1) Measure type is actually not a measure dimension, it is "measure type" dimension. I have plenty of measures like: quantity, value, price, etc (to simplify) and in measures I don't have a top dummy member. Beside measures I have a Measure type dimension". In cube there are about 10 measures and I have about 20 members in "Measure types" dimension. One of the solution would be to combine this two dimensions into measures alone, but then I would get 10 x 20 = 200 measures. I would like to avoid so much measures if possible, that is the reason I have split measures into real measures and measure types.
2) I know "\" operator produces 0 instead of #N/A, but this is not the problem I have. I want to have N/A value, just wondering if there is some other way of getting it beside producing dummy matematical division by zero formula: ['Measure type']=0/0;

Maybe this is not the best sample I have. I also have a for example dimension Price type with two members temporally price and final stock price. I have plenty of measures and some of the measures are allowed to have values at both prices from Price type dimension, but some of the measures should not have a value (also zero should not be the value) for temporally price. The solution I see is the "not applicable" value like #N/A in this case for temporally price member in conjunction with some of the measures.

[deepakjain2020]

Try below code

['Measure type']=S: 'N/A ';

Regards,
Deepak Jain

No. In order for this to work Measure Type would have to be defined as S (string) and it is clear he has it as a C (consolidation). If he were to change it to S then it would not be the parent node and he would lose the defined hierarchy. You just need to learn to live with it. It is extremely minor and trust me, you are going to encounter a lot of similar type things if you continue with TM1. It goes with the territory.

Thanks Tomok, for correcting my mistake and sharing it.
I am feeling happy to be a part of TM1Forum. My silly mistakes are making me to learn.

Another approach

When i get some time will try to have attribute and assign 'N/A' and then try to write a rule to check.

Regards,
Deepak Jain

[Steve Rowe]

The short answer is no, there is no object that you can set a numeric cell to that will give you the NA, your solution seems good.

If you wanted to make you rule more readable you could set up a measure called "NA" in a 2d cube and rule it =0/0 and link to this in you main rule but it hardly seems worth the effort....

Cheers,

[gstager]

Your code seems an elegant solution. Wouldn't a simple comment work to help those who come across this later?

[Wim Gielis]

Why not using the function UNDEF ? Or am I missing something obvious here?

But note that me personally I never used this function, as I always force meaningless consolidations to 0.

[declanr]

Beside this problem, a year ago I have created some string member and Cognos BI only supports numbers as measure values, so members in TM1 member type has to be defined as N or C. So S type of member is not allowed in Cognos BI (at least it wasn't at the time I have tested, now some fixpacks were applied so haven't tested the latest and the greatest).

Just to take a side track as a result of this comment. Passing string measures from a TM1 source to BI is perfectly acceptable; it is only crosstab BI reports that won't display the contents, other report types such as lists will allow you to show the contents of string measures.

[abcuser]

@Wim Gielis, thats it! Thanks a lot, so solution is:

Code: Select all

['Measure type']=UNDEF;

This is more readable (comparing to my dummy mathematical formula division by zero), becasue everyone can read the UDEF definition from TM1 Reference Guide.

@gstager, adding additional comment as you suggested is also additional benefit.

@declanr, I only tested crosstab. Thanks for this info!

[rmackenzie]

Abcuser, did you consider element weighting to solve the problem? If you assign 0 weighting to the children of 'Measure Type' then this would disable its aggregation. This would be more efficient than writing a rule on that particular cube.

[Wim Gielis]

True Robin, but it still shows 0...

[sachin]

@abcuser

Apart from several inputs that others have chimed in, I am going to suggest an alternative. Hopefully it works out for you.

Foremost of all, you can display text in a crosstab! Yes, it is possible. Several years back I had done it. Refer to this IBM KB in achieving it -
http://www-01.ibm.com/support/docview.w ... wg21454433

Secondly, unlock your crosstab, and define the content of the intersection to "Yes"

Crosstab_Pic.jpg (26.21 KiB) Viewed 14823 times

Drag and drop a string from available objects and put your text. See a sample output below

[rmackenzie]

True Robin, but it still shows 0...

Yes, I realise that's not what the OP asked. However, this solution:

Code: Select all

['Measure type']=UNDEF;

Could potentially present a worse problem than the cosmetic inconvenience of the 0 being against 'Measure Type' as a result of negated dimension consolidation. If the cube is large cube (by theoretical volume) then a lot of work has to be done to populate every intersection, including consolidations, for that measure, with -1.#QNA. That's what I get on 10.1.1 from an cube export where the UNDEF rule is applied.

[Wim Gielis]

That's also true, Robin. I noted in my previous post that I never used the function.
And I should have added to that phrase that I will most likely never use the function.

[abcuser]

@rmackenzie, I have several rules, this tiny rule does not add additional noticable response time in my case. Probably because it is not the biggerst cube in the world.

@sachin, I need to have this solution in cube not in report. End-users can create new report and this "zero" problem should also not appear new reports.