Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try this as the calculated dimension:
=if(Len(Trim(ReqDate)) = 0,SO#)
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
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.