How to create 3 values from one date field variable

[#1]

Hi All ,

Would it be possible to create 3 values from one date filed.what I am expecting here

If date field is null show as Pend , if date field is not null show as complete and want to show if date field is total as Recieved

This is expecting

if(isnull(date ),'Pend',if(not Is null(date),'complete ',if(isnull(date)and if (is not null(date),'received') as newfield

I am able to getting two values that Pend ,complete

How to get two values total as received

Please guide me how to do it

[#2]

Script:

  if(len(DATE)=0,1,0) as Null_Flag,

Expressions:

Received

count(ID)

Completed:

  count({<Null_Flag={0}>}ID)

Pending:

count({<Null_Flag={1}>}ID)

[Kushal_Chawda]

What you want to do while selecting from the bar?

[#4]

Assume, Received variable combination of both values ,if user select Pending ,pending values need to show on Received bar right . the same way for completed .we can show the values if I use your logic its mean read only values not select the bars isn't it . I know there is another way to show both values total use select total option(dimension limit) and rename total label as 'Received' but there is another problem for sorting . please guide me if I am wrong

[sunny_talwar]

Can you share your script you used to create the link table? This below should have worked...

LINKTABLE:

LOAD 'Pending' as Flag,

          UniqueIdentifierFieldHere

From YourFactTable

Where Len(Trim(date)) = 0;

Concatenate(LINKTABLE)

LOAD 'Complete' as Flag,

          UniqueIdentifierFieldHere

From YourFactTable

Where Len(Trim(date)) > 0;

Concatenate(LINKTABLE)

LOAD 'Received' as Flag,

          UniqueIdentifierFieldHere

From YourFactTable;

[sunny_talwar]

That is not what I recommended my friend.

[#7]

LINKTABLE1:

LOAD Distinct
'Pending' as Flag,
ID,
DATE
FROM
………    (qvd)
Where Len(Trim(DATE)) = 0;

Concatenate(LINKTABLE1)

LOAD Distinct
'Complete' as Flag,
ID,

DATE

FROM

……………………………………………… (qvd)
Where Len(Trim(DATE)) > 0;



Concatenate(LINKTABLE1)

LOAD Distinct
'Received' as Flag,
ID,

  DATE

FROM

able to see the values but duplicates values added in 'Pending'

original values chart below :

[sunny_talwar]

Can you try this without adding DATE field to the LINKTABLE?

LINKTABLE1:
LOAD Distinct
'Pending' as Flag,
ID
FROM
………    (qvd)
Where Len(Trim(DATE)) = 0;
Concatenate(LINKTABLE1)

LOAD Distinct
'Complete' as Flag,
ID

FROM

……………………………………………… (qvd)
Where Len(Trim(DATE)) > 0;

Concatenate(LINKTABLE1)
LOAD Distinct
'Received' as Flag,
ID

FROM

[#9]

nothing changes ,still in same position

[sunny_talwar]

Is ID Truly unique for each row of your main table?

[#11]

Yes Sunny, it is