Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
NewToQV
Contributor III
Contributor III

Return specific value from straight table

I have a straight table where I have 4 dimensions and multiple expressions just showing the value of a cell from a staged excel file.

I need to return specific values through out the straight table. The table has been frozen so it cannot be sorted etc.

Example table below. My 4 dimensions are as of date, partner, partner product, min.max.

I need to be able to pull specific Need values in the columns.

Example: I need to pull the a.2 min needed value 1 and display it in a text object

Then in another text object I need to display the A.2. max value in a text object.

So on and so forth. 

Thank you ahead of time!

As Of DatePartnerPartner ProductMin/MaxNeeded values 1Needed values 2Needed values 3Needed values 4
1/1/2018AA.1Mintest 1value 1needed 1last 1
1/1/2018AA.1Maxtest 2value 2needed 2last 2
1/1/2018AA.2Mintest 3value 3needed 3last 3
1/1/2018AA.2Maxtest 4value 4needed 4last 4
1/15/2018BB.1Mintest 5value 5needed 5last 5
1/15/2018BB.2Maxtest 6value 6needed 6last 6
1/15/2018CC.1Mintest 7value 7needed 7last 7
1/15/2018CC.2Maxtest 8value 8needed 8last 8
1 Solution

Accepted Solutions
Shubham_Deshmukh
Specialist
Specialist

Try this:
=Only(if([Partner Product]='A.1' and [Min/Max]='Min',[Needed values 1]))

View solution in original post

13 Replies
Shubham_Deshmukh
Specialist
Specialist

Try this:
=Only(if([Partner Product]='A.1' and [Min/Max]='Min',[Needed values 1]))
NewToQV
Contributor III
Contributor III
Author

Hey @Shubham_Deshmukh , thanks for your quick response. Looks to be doing exactly what is needed. Thank you!!

 

One more question. There may be multiple dates for that particular 'Partner Product'. How can I also ask to use the maximum date from the 'As of Date' field in addition?

Shubham_Deshmukh
Specialist
Specialist

Hope you are expecting this,

=MaxString({<[As Of Date]={"=$(=max([As Of Date]))"},[Partner Product]={'A.1'} , [Min/Max]={'Min'}>}[Needed values 1])
//=Only(if([Partner Product]='A.1' and [Min/Max]='Min',[Needed values 1]))[As Of Date]={"=$(=max([As Of Date]))"},

 

Regards

NewToQV
Contributor III
Contributor III
Author

Hello @Shubham_Deshmukh . Thanks again. However when using the below I am receiving and 'Error: Error in set modifier expression'.

=MaxString({<[As Of Date]={"=$(=max([As Of Date]))"},[Partner Product]={'A.1'} , [Min/Max]={'Min'}>}[Needed values 1])

I left out the commented out portion, does that need to be included? Thoughts?

Shubham_Deshmukh
Specialist
Specialist

See my exp, working absolutely fine and getting result,please check you square and curly braces once again. Commented line does not have any effect on expression.

rf.png

NewToQV
Contributor III
Contributor III
Author

Strange. All I have done is copy and paste your expression and change the names of my fields to what I have. The previous expression without the date is working perfectly.

Untitled.png

 

Shubham_Deshmukh
Specialist
Specialist

Hi ,

Look at my data, this is what I am using and expression is same which I mentioned earlier,

eef.png

Above data and that expression give result = testOutput - which is I think correct output.

NewToQV
Contributor III
Contributor III
Author

Based on what I see below, the result 'testOutput' is correct and is exactly what I am looking to do.

I've made sure my expression matches yours and the expression just returns a hyphen '-'

I guess these something wrong with my date format?

I have an excel file which I am loading. Just as you did I placed a dummy row with a new date in the As Of Date for the same partner product. The column 'As Of Date' in the excel file is formatted as a date (m/d/yyyy).

Any other thoughts as to why it would just return a hyphen?

Shubham_Deshmukh
Specialist
Specialist

I changed the date format intentionally, if possible you change your date format and then try.