Return specific value from straight table

[NewToQV]

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 Date	Partner	Partner Product	Min/Max	Needed values 1	Needed values 2	Needed values 3	Needed values 4
1/1/2018	A	A.1	Min	test 1	value 1	needed 1	last 1
1/1/2018	A	A.1	Max	test 2	value 2	needed 2	last 2
1/1/2018	A	A.2	Min	test 3	value 3	needed 3	last 3
1/1/2018	A	A.2	Max	test 4	value 4	needed 4	last 4
1/15/2018	B	B.1	Min	test 5	value 5	needed 5	last 5
1/15/2018	B	B.2	Max	test 6	value 6	needed 6	last 6
1/15/2018	C	C.1	Min	test 7	value 7	needed 7	last 7
1/15/2018	C	C.2	Max	test 8	value 8	needed 8	last 8

[Shubham_Deshmukh · Accepted solution]

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

[NewToQV]

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]

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]

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]

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.

[NewToQV]

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.

[Shubham_Deshmukh]

Hi ,

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

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

[NewToQV]

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]

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