If then statement problem.

[Anonymous]

I created this expression for a calculated dimension:  If(ProgressStatus = 'PastDue', 'Past Due', 'On Time')

I am using it to split the data. Some of the projects fall in both categories apparently so they are being dulicated. Can you help me make them so that they are not duplicated. If they are duplicated they should be Past Due, as anything that contains Past Due should be past due and not on time at the same time. I have attatched my data, and the 3 items being duplicated have been highlighted in yellow.

Thanks in advance!

[OmarBenSalem]

I'd do such a thing in the script as this :

Suppose we have a table named table containing 3 fields, one field is named ProgressStatus and has as values PastDue and else.

Here is what I would do:

table:

Load

ProgressStatus,

field2,

field3,

from your source;

"Final Table":

load field2,

field3,

'Past Due' as "Progress Status" //this is a new field

resident table where ProgressStatus=PastDue;

concatenate

load field2,

field3,

'On Time' as "Progress Status"

resident table where ProgressStatus<>PastDue;

Drop table table;

[Anonymous]

The thing is we cannot manipulate the data load, this is only the data that is being used for this visualization, this application contains much more data than what I have attatched.

[OmarBenSalem]

Something like this would do the trick?

As dimensions, I chose Name, Status

My Measure (New Status) = if(Aggr(COUNT(Status),Name)=2,Status='Past Due', Status)

[Anonymous]

No as the OnTime and Past Due is not in the actual data. It is a calculated dimension that I created. I don’t think this would work unless the Ontime and Past Due were in the original data.

[OmarBenSalem]

I worked on you created field containing On time and Past Due. and then forced it to only show Past Due if records are being duplicated.

Can you please provide me the original table? So I can work on?

Thanks,

[CarlosAMonroy]

Hi Ariana,

You can try:

=if(isnull(ProgressStatus),'Null',if( aggr(count(ProgressStatus),Name,ItemNumber)=2 or ProgressStatus='Past Due','Past Due','On Time'))

Hope that helps,

Carlos M