Min and Max values from selected dimensions

[AFigueiredo]

Hello everyone,

I'm currently working on a pivot table, and I've encountered a situation.

Let's say I have this in my pivot table:

Name > Type > Number > Source

where type is either Cat or Dog, and my Number is a set of 8 digit numbers (although I want it to retrieve 3 digits, with a mid function).

What I'm looking for is something like if my Type is Cat, I want to fetch the minimum value from the selected Number field (not the entire data, but the narrowed data with Name and other previous conditions).

I've tried the following:

if(Type = 'Cat', min(mid(number(3,3)), max(mid(number(3,3)))

as in: if the type is a cat, I want it to get me the minimum value that is set by the 3rd to 5th digit, and the maximum otherwise (if the type is a dog).

However, I'm only getting "// Error in calculated dimension". This isn't a static dimension either, as it depends on the selected fields.

I've attached a table with a picture of how it is right now, and how I intend it to be

Not sure if I've been clear enough to get an answer, so I'll try to explain with more detail if needed.

Thanks in advance.

[chrismarlow · Accepted solution]

Hi,

I am not sure about using a pivot table like this, or the calculated dimension bit, would be inclined to look at the data model.

However if you cannot do that but could use a straight table you could try the below;

Here Name and Type are dimensions and the other two are expressions;

If(Type='Cat',Min(Mid(Number,3,3)),Max(Mid(Number,3,3)))

FirstSortedValue(Source,if(Type='Cat',Mid(Number,3,3),-Mid(Number,3,3)))

Cheers,

Chris.

[AFigueiredo]

Hello! 

Thank you for answering, that was very helpful and helped me sort this out, partially. I've also changed to a straight table, as suggested.

I've now encountered another problem:

Let's say that for each number associated to Cat, there is 1 Source value,

but for each number associated to Dog, there are 5 Source values. 

Your solution works great for Cat, as I only have one source number associated, but for Dog, my output is ' - ', as there are 5 different values. Is there a way to show the 5 of them?

I've attached a picture so that you could get a better understanding.

Also, is it possible to merge cells with the same value in straight tables? (this is for aesthetic / readability purposes only, not really important)

Regards,

António

[chrismarlow · Accepted solution]

Hi,

Maybe change Source to be a dimension & just have 1 expression;

IF(If(Type='Cat',Min(TOTAL Mid(Number,3,3)),Max(TOTAL Mid(Number,3,3)))=Mid(Number,3,3),Mid(Number,3,3),Null())

So;

I will have a think about the formatting, but not sure I've seen it done.

Cheers,

Chris.

[chrismarlow]

Hi,

So this is horrible and I am sure will break, but try it anyway.

Add a second expression (that you can then hide on Presentation tab;

IF(If(Type='Cat',Min(TOTAL Mid(Number,3,3)),Max(TOTAL Mid(Number,3,3)))=Mid(Number,3,3),
If(RowNo(TOTAL)=1,1,IF(Only(Name)<>Above(Only(Name),-1),1,0)),
Null())

Then on Dimension tab refer to that field in the Text Color of Name dimension;

=if(Column(2)=1,Black(),White())

Might hold together, might do odd things depending on what else you do with it.

Cheers,

Chris.

EDIT - you need to add back in the Name & Type field on the TOTAL;

IF(If(Type='Cat',Min(TOTAL <Name, Type> Mid(Number,3,3)),Max(TOTAL <Name, Type> Mid(Number,3,3)))=Mid(Number,3,3),Mid(Number,3,3),Null())

IF(If(Type='Cat',Min(TOTAL <Name, Type> Mid(Number,3,3)),Max(TOTAL <Name, Type> Mid(Number,3,3)))=Mid(Number,3,3),
If(RowNo(TOTAL)=1,1,IF(Only(Name)<>Above(Only(Name),-1),1,0)),
Null())

[AFigueiredo]

Hi,

I've accepted both this one and the first post as solutions, since I managed to work things out in terms of data management thanks to them.

I haven't tested the formatting expressions yet, but I'll give it a go whenever I can.

Once again, I'm very thankful for your help!

Regards,

António