Set Analysis with Data in the Table

[NikhilGurg]

Hi Folks,

I have a table like below,

 In this table, all the column are calculated as measures, however, QTD Target is kept as (=Min(QTD_target)) and Product is used as dimension.

I have another table as shown in left which acts as a lookup value,

So basically i need to divide sales by target and look up that value in ATTN to get ACHV.

For this I am using the below formula as measure in first table,

=FirstSortedValue({<ATTN ={"<=$(=$(Sales/Min(QTD_Target)))"}>} ACHV,-ATTN)

However, in this case the Min(QTD_Target) is taking the value from the above designed table rather than actual data i.e. 288,126. Similarly, on taking max it is taking the max value 3584190.

The problem is that it is taking same value of QTD_Target for both the rows and not taking value of each row in the calculation.

Where am i going wrong.

Please help.

[sunny_talwar · Accepted solution]

The problem is that the set analysis is evaluated once per chart and not per dimension... so, in order to do this... you would need to use if statement within Aggr() function.... 

[NikhilGurg]

Hi Sunny,

Thanks a ton for your response.
However, can you help me with an expression here as well.
Kind of struggling whole night so almost lost my mind.

Just to add, if you are referring to IF statement over the second table, then that is not a viable solution for me as the value of second table may keep on changing. 

Regards.

[NikhilGurg]

Any Help will be highly appreciated.

[sunny_talwar]

It would be easier to help if you are able to share a sample.

[NikhilGurg]

Sure, Below is the attached QVF.
If you check the column lookup, it is giving me the value for first because i have used Max, but the problem is it is giving the same value in both the rows.

The workbook is IC Testing Dashboard.

Top Row, Right table, I calculate the attainment (Sales/Target). Bottom RIght, I have lookup table of ATTN (Attainment) and AcHV (Achievement). I need to get the achievement in each row based on attainment value from the lookup table.

All the hit and trials that I have tried are put in the expression of 'lookup' column as comment.

[sunny_talwar]

Why is your QTD Sales 0? I mean not that it matters, but to actually test out anything... I would have hoped for an attainment number

Would you be able to give me values for the vSales1M1, vSales1M2 etc... so that I can have some value. Also, based on the input provided.. it would be helpful if you can provide me with the output you are expecting to see.

[NikhilGurg]

Apologies, I forgot to mention,

1. please filter (where RAJMA or ID - left of  "target pay") is mentioned as "MELANY STRYDOM" so that environment is set for your usage (which can be confirmed with value in "target pay").

2. please put some numbers in the input text of Oct, Nov & Dec and you will see that attainment will be generated.

Secondly, please do keep a note that the lookup table (ATTN ; ACHV) is a dynamic table (values are not constant and can keep changing for both columns).

If possible, please let me know if we can connect, (nikhilgurg@gmail.com) Just in case any input which can be shared from my end.

[NikhilGurg]

Thanks a lot sunny for engaging with me.

The issue is resolved. 🙂