Check if for dimension value exists already another expression value
I've tried every option I know so I hope somebody can help me out.
First of all : I need to achieve this by set expression, so calculations in the script are not a good solution.
I've got a chart(table) with Date (dim), Order number (dim), Line number (dim), Article number (dim), some other dimensions and some expressions like quantity, value, etc.
This chart is sorted by date.
Important: This chart is already pre-filtered by adding some show_ if-conditions to multiple dimensions. So the count conditions, if used in expressions, must count in the resulting chart and not in the database set of records.
Now I want to have an expression column that can check for every Article number if it only exists once or not. If not I want also to determine what is the first occurence in the table.
I'm thinking of creating a new field that combines Order number and Order line so I have a unique field (let's say "NR_LINE") and I want to combine that with a count for every article number how many "NR_LINE" there are.
If this count comes op with a result of more than "1" then I need to determine if this is the first occurence or nor (maybe something with below() or above() )
If we get part (I) right I would also like to take it somewhat further and for every article number that comes up the 2nd time or more, it should mention the Order Number of the previous occurence (2nd time Article number shows 1st time Order number, 3rd shows 2nd, and so on)
Generally it is most helpful if you attach your source data or QVW you have along with what you want to see, otherwise not betting you are going to get a response from anyone.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
"-Important: This chart is already pre-filtered by adding some show_ if-conditions to multiple dimensions. So the count conditions, if used in expressions, must count in the resulting chart and not in the database set of records."