Year field is having blank year with some data

[VenkateshYarra]

HI Team,

I have a year filed with blank year in it, but the bank year rows also having some value, i need to fetch complet data.

Below is my expresion in water fall chart.

Sum({<CURRENT.PROJECT_ID ={"=Only({<[HIST.Extraction Date] ={'$(vHistExDateTimeStamp)'}
,[HIST.Project.Contract Signature Date.FY]={'*'}-{'$(vYear)'}
,[HIST.Project.Status (Grouped)]={'*'}-{'Active'}
,[HIST.Project.Milestone Achieved]=,[CURRENT.Project.Status (Grouped)]=,[CURRENT.Project.Contract Signature Date.FY]=>}HIST.PROJECT_ID)
"},
[CURRENT.Extraction Date] = {'$(vActExDateTimeStamp)'},[CURRENT.Project.Status (Grouped)]={'Active'},[CURRENT.Project.Contract Signature Date.FY]= {'$(vYear)'}
,[HIST.Project.Milestone Achieved]=,[HIST.Project.Status (Grouped)]= ,[HIST.Extraction Date] =>}[CURRENT.Project.Contract Value M€])

i am using {'*'}-{'$(vYear)'} to get all the data, but i am getting the data exlcuding the the bank in year field.

vYear ='(' & GetFieldSelections([HIST.Project.Contract Signature Date.FY], '|') & ')'

Can anyone correct me in getting the correct value.

Thank you in advance

Regards

Venkatesh

[rwunderlich · Accepted solution]

Firstly, although this does not address your blank year question, '*' is the wrong way to select wildcard. It should be "*". (double quotes).  The single quotes will work on some older apps that were created before the bug was fixed.  But single quotes will not work in newer apps. 

And the * is not needed in your expression. Instead of:

{"*"}-{'$(vYear)'}

Just write

= -{'$(vYear)'}

For example:

[HIST.Project.Status (Grouped)]= -{'Active'}

Regarding your problem of blank Year.  Blank (zero length string) and nulls cannot be selected by set analysis.  What you need to do is select some other field (like "ID") that has a value for every row and indirectly exclude based on other fields using E().

<ID = E({<[HIST.Project.Status (Grouped)]= -{'Active'}>})>

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com