8 Replies Latest reply on Oct 23, 2018 6:23 PM by lei.chen.0

Change in value of Default Flag

Hi,

Questions:

1) Display only the customer with a change in the status when comparing April to July.

Here my expected output should only 'Client B'

2) Display customer with change in the status during the period

Here the expected output should be 'Client B' and 'Client C'

Got some solution from you similar like this from blow thread

It would be great if i get correct solution what i have requested above 2 scenario.

Kavi

• 1. Re: Change in value of Default Flag

Hello Ravi,

You can try below approach:

I am assuming your data is in below format:

If yes, try below logic:

Max month status =  if { FIXED [Client]: max(DATETRUNC('month',[Date]))}=DATETRUNC('month',[Date]) then [Status] END

Min month status =  if { FIXED [Client]: min(DATETRUNC('month',[Date]))}=DATETRUNC('month',[Date]) then [Status] END

Question 1 =

if {FIXED [Client]:max([Max month status])}<>

{FIXED [Client]:max([Min month status])}then [Client] END

Question 2 = if { FIXED [Client]:COUNTD([Status])}<>1 then [Client] END

• 2. Re: Change in value of Default Flag

Hello kavi,

In addition to MEENU's solution, I played a little with your scenario.

The data format is the same as MEENU's assumption.

Select a start month and an end month, and you will get two lists of clients.

The workbook is attached for your reference.

Regards

Lei

• 3. Re: Change in value of Default Flag

Thank you Meenu !!!..

• 4. Re: Change in value of Default Flag

Hi Lei Chen,

Thank you very much for the solution as expected.

And also can you pleas help me to so the flag status like,

if the status change from Y to N then show as ' Y -> N'

if  the status change from N to Y then show as ' N -> Y'

else 'No change'

Regards,

Kavi

• 5. Re: Change in value of Default Flag

Hello kavi,

IIF(MAX([Status Start]) = MAX([Status End]),

'Still '+MAX([Status Start]),

MAX([Status Start])+' -> '+ MAX([Status End]))

The result looks like,

Regards

Lei

• 6. Re: Change in value of Default Flag

Hi Lei Chen,

Thank you very much for you answer . And also i have tried some analysis for "Null" flag using your workbook but i could not meet the actual result.

So the output as like below,

IF Start Flag = Y AND End Flag = N then ‘Exit From Default’

IF Start Flag = N AND End Flag = Y then ‘New Default’

IF Start Flag = Null AND End Flag = Y then ‘New Default’

IF Start Flag = Null AND End Flag = N then exclude record (not to display in the view)

IF Start Flag = Y AND End Flag = Null then ‘No More Exposure’

IF Start Flag = N AND End Flag = Null then exclude record (not to display in the view)

New data set is like below,

I have tried like below calculation but could not get the actual result.

The above calculation is displaying as ,

Client H should not display in the view as request.

I have attached the workbook with new updated data.

Kind Regards,

Kavi

• 7. Re: Change in value of Default Flag

I have tried little more and i got the answer,

Below is the formula i used ,

Thank you very much for your kind reply for this solution Lei Chen .

Regards,

Kavi

• 8. Re: Change in value of Default Flag

Hello kavi,

Happy to know that you've got the answer and thanks for sharing

Regards

Lei