Report record with blank date

[tracycrown]

Dear all

I am trying to generate a report containing records with blank ReqDate (ie. last two records) using if(ReqDate='',SO#) in dimensions but it does not work, please advise.

SO#	OrdDate	ReqDate	OrdAmt
1001	09/01/2014	14/01/2014	200
1002	13/03/2014	22/03/2014	300
1003	24/04/2014	28/04/2014	1,200
1004	24/06/2014	200
1005	24/06/2014	400

Thank you so much

Tracy  

[sunny_talwar]

Try this as the calculated dimension:

=if(Len(Trim(ReqDate)) = 0,SO#)

[sunny_talwar · Accepted solution]

Although a more efficient method would be to use a set analysis expression:

Sum({<SO# = {"=Len(Trim(ReqDate)) = 0"}>}OrdAmt)

With SO#, OrdDate, ReqDate as Dimensions

[#4]

Tracy, add the Flag field on the table at script level.

Load

     [SO#],

     OrderDate,

     RequestDate,

     Amount,

     IF(Len(Trim(RequestDate)),1,0) as IsRequestDateBlank

From SourceTable

;

on UI, create calculated Dimension or Set expression to show blank request date

Calculated Diemension:

IF(IsRequestDateBlank=0,[SO#],null() and Select Suppress when Dimension Value is null on the dimension tab.

Set Expression:  Sum({<IsRequestDateBlank={0}>}Amount)

You really don't need a Flag field but if we have huge data set Flag with set analysis may give better performance.