Set Analysis with blank dates Query

[#1]

I am having problems with the following Set Analysis query.   What I am wanting to extract are all records where an "AssetAcquiredDate" exists and the AssetSoldDate is either after the selected Month/Year or is blank (this means as at a selected period end, the item was still on the books).

The following query works, apart from the "len(trim(AssetSoldDate))=0".  These items are being excluded - have I missed something in the syntax??

sum({$<AssetAcquiredDate={">=$(=Date('01/01/1900'))"},

AssetSoldDate={">=$(=monthend(max(SelectDate)))" , "len(trim(AssetSoldDate))=0"}

>} JCCostsTotal)

[jagan]

Hi,

Try like this

In script arrive a flag for null dates like below

LOAD

*,

If(len(trim(AssetSoldDate))=0, 1, 0) AS IsAssetSoldDateNull

FROM DataSource;

Sum({$

     <AssetAcquiredDate={">=$(=Date('01/01/1900'))"}, AssetSoldDate ={">=$(=monthend(max(SelectDate)))"}> +         <AssetAcquiredDate={">=$(=Date('01/01/1900'))"}, IsAssetSoldDateNull={1}>} JCCostsTotal)

Hope this helps you.

Regards,

Jagan.

[anbu1984]

Try this

"=len(trim(AssetSoldDate))=0"

[#4]

I tried this and it did not make any difference.

[PrashantSangle]

Hi,

Did you try with simple if - else condition

like

sum(if(len(trim(AssetSoldDate))=0,

sum({$<AssetAcquiredDate={">=$(=Date('01/01/1900'))"}>} JCCostsTotal),

sum({$<AssetAcquiredDate={">=$(=Date('01/01/1900'))"},

AssetSoldDate={">=$(=monthend(max(SelectDate)))"}

>} JCCostsTotal)

Regards

[anbu1984]

Then you have Nulls in Date field instead of blanks. Replace ID with key field or non-nullable field

sum({$<AssetAcquiredDate={">=$(=Date('01/01/1900'))"},

AssetSoldDate={">=$(=monthend(max(SelectDate)))"}> + <AssetAcquiredDate={">=$(=Date('01/01/1900'))"},ID={"=len(trim(AssetSoldDate))=0"}

>} JCCostsTotal)

[#7]

Thanks - this helped to get closer to a solution!  I found the following worked:

//  Blank Asset Sold Date

if(len(trim(AssetSoldDate))=0,

    sum({$<AssetAcquiredDate={">=$(=Date('01/01/1900'))<$(=monthend(max(SelectDate)))"}>} JCCostsTotal),

// Else Asset Sold Date is after selected Month   

    sum({$<AssetAcquiredDate={">=$(=Date('01/01/1900'))<$(=monthend(max(SelectDate)))"},

    AssetSoldDate={">$(=monthend(max(SelectDate)))"}

    >} JCCostsTotal))

However I have now found the pivot table totals are not correct.  Is this because there are two "sum" statements?  The system is only including values in the total where there was a sale in a future month:

[jagan · Accepted solution]

Hi,

Did you tried this expression

LOAD

*,

If(len(trim(AssetSoldDate))=0, 1, 0) AS IsAssetSoldDateNull

FROM DataSource;

Sum({$

     <AssetAcquiredDate={">=$(=Date('01/01/1900'))"}, AssetSoldDate ={">=$(=monthend(max(SelectDate)))"}> +         <AssetAcquiredDate={">=$(=Date('01/01/1900'))"},IsAssetSoldDateNull={1}>} JCCostsTotal)

Regards,

Jagan.

[#9]

Thanks - I have tried this approach and it is now working!  This is the syntax I ended up using:

// Asset Acquired Date is not blank and Asset Sold Date is either after this month or is blank.

Sum({$<AssetAcquiredDateIsNull={0}, AssetSoldDate ={">$(=monthend(max(SelectDate)))"}> +

    <AssetAcquiredDateIsNull={0},AssetSoldDateIsNull={1}>

    } JCCostsTotal)