topic Re: Set Analysis with blank dates Query in QlikView

Set Analysis with blank dates Query

Tue, 24 Feb 2015 05:19:50 GMT

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)

Re: Set Analysis with blank dates Query

jagan — Tue, 24 Feb 2015 05:45:39 GMT

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.

Re: Set Analysis with blank dates Query

anbu1984 — Tue, 24 Feb 2015 05:57:51 GMT

Try this

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

Re: Set Analysis with blank dates Query

Tue, 24 Feb 2015 06:40:16 GMT

I tried this and it did not make any difference.

Re: Set Analysis with blank dates Query

PrashantSangle — Tue, 24 Feb 2015 06:52:06 GMT

Hi,

Did you try with simple if - else condition

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

Re: Set Analysis with blank dates Query

anbu1984 — Tue, 24 Feb 2015 07:01:02 GMT

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)

Re: Set Analysis with blank dates Query

Wed, 25 Feb 2015 00:12:00 GMT

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:

Re: Set Analysis with blank dates Query

jagan — Wed, 25 Feb 2015 02:31:02 GMT

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.

Re: Set Analysis with blank dates Query

Wed, 25 Feb 2015 05:34:29 GMT

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)