Script

[yrstruly]

Can Somebody please Help me with this script?

If the "AUTHORISED AMOUNT" is R0, use the field "CLAIMED AMOUNT" as a value.
	For this view, you should only include claims where the CLAIM STATUS is not equal to 'R' or 'Z'.

[pradosh_thakur]

may be this

if("AUTHORISED AMOUNT" ='R0' AND NOT MATCH([CLAIM STATUS],'R','Z'),"CLAIMED AMOUNT" )

[yrstruly]

Thank You for replying. My visualisation goed from colour to grey when i apply it. What am i doing wrong? Here is access to the data files https://drive.google.com/folderview?id=1mQFlR8DfJBGEoF7rZdIJVEcw_F7n9FBq

[juraj_misina]

Hi,

It's not clear whether you need to do the adjustment in script (data load) or measure/expression. If in script, then @pradosh_thakur gave a good suggestion. If in measure, I'd go with set analysis:

Sum({<[AUTHORISED AMOUNT]={'R0'}, [CLAIM STATUS]-={'R', 'Z'}>} [CLAIMED AMOUNT])

Hope this helps.

Juraj

[yrstruly]

Hi Juraj
Thank you for the feedback. Here follows the instruction(im not sure at which measure i should add the script)? "Display the total of "AUTHORISED AMOUNT" for all claims of which the "INCIDENT DATE" fell within the date range of each
Catastrophy start and end date (Excel Document 'Catastrophy Incidents'). If the "AUTHORISED AMOUNT" is R0, use the field "CLAIMED AMOUNT" as a value.
For this view, you should only include claims where the CLAIM STATUS is not equal to 'R' or 'Z'."

[yrstruly]

Can you please advise at what measure i should enter the expression. I have published the App. You can also review my data attached?

[juraj_misina]

Hi,
the requirement is not clear to me.
- There's no "AUTHORIZED AMOUNT"='R0', in fact, there are only numeric values, but those values which contain decimal separator are not interpreted correctly (they appear as text, not as number, so you need to correct this in script with Num#() function. EDIT: Done in script.)
- The requirement says 'If the "AUTHORISED AMOUNT" is R0, use the field "CLAIMED AMOUNT" as a value.' I ask: what should I do in all other cases?'
- You mention an excel "Catastrophy Incidents', but it is not loaded. (EDIT: I loaded the file from attached source)
- Most importantly: the data model is not correct. You associated the ClaimsMaster and ClaimsListing with "CLAIM NUMBER" field, but this is not unique identifier for different policies. I think - with my very limited knowledge of the requirement - that you should use a compound key Policy*ClaimNumber or something similar (EDIT: see attached app and check if this seems correct).
You can try uploading sample/mock data, if you want, maybe we'll be able to move forward with that.

EDIT: Thanks for reminidng me about the link to data. Check the attached app, most of points still stand though.