topic Re: IF Function field in Set Analysis in QlikView

IF Function field in Set Analysis

Tue, 09 Jul 2013 09:56:57 GMT

Hey,

I need to apply date filter on expression in a table, which has values that are not connected with any CREATEDATETIME. So I need to apply this filter only on fields which are not null.Here is my set analysis which won't work.

sum ({<[$(=if(not isnull(ZpoGr.CREATEDDATETIME),ZpoGr.CREATEDDATETIME))]= {$(='">='&DATE(zpgCreateDT_From2) & '<=' &DATE(zpgCreateDT_To2) & '"')}>}InvOut.AMOUNTCUR)

Thanks for help

UPD I have problems only with the left part of the expression - the right one works correctly

Re: IF Function field in Set Analysis

Tue, 09 Jul 2013 10:01:27 GMT

sum ({<

[$("=if(ZpoGr.CREATEDDATETIME<>' ',ZpoGr.CREATEDDATETIME)"]= {$(='">='&DATE(zpgCreateDT_From2) & '<=' &DATE(zpgCreateDT_To2) & '"')}>}InvOut.AMOUNTCUR)

Re: IF Function field in Set Analysis

Tue, 09 Jul 2013 10:05:47 GMT

the same result

Re: IF Function field in Set Analysis

Sokkorn — Tue, 09 Jul 2013 10:07:32 GMT

Hi,

Can you try this set analysis and let me know the result

=Sum({$<[ZpoGr.CREATEDDATETIME] = {">=$(=Date([zpgCreateDT_From2]))<=$(=Date([zpgCreateDT_To2]))"} >} [InvOut.AMOUNTCUR])

Note: make sure you have same date format in field [ZpoGr.CREATEDDATETIME],[zpgCreateDT_From2] and [zpgCreateDT_To2]

Regards,

Sokkorn

Re: IF Function field in Set Analysis

Tue, 09 Jul 2013 10:10:48 GMT

the problem is that I have to check if the value is not null, cause without this check the filter reduces all lines which have no CREATEDDATETIME even when the filters are empty

Re: IF Function field in Set Analysis

Sokkorn — Tue, 09 Jul 2013 10:21:01 GMT

Hi,

=Sum({$<[ZpoGr.CREATEDDATETIME] = {">=$(=Date([zpgCreateDT_From2]))<=$(=Date([zpgCreateDT_To2]))"},
[ZpoGr.CREATEDDATETIME] -= {$(=IsNull([ZpoGr.CREATEDDATETIME]))}
>} [InvOut.AMOUNTCUR])

Second line of set analysis above will exclude null value from your field. Let give it a try. If possible, attacched your sample att here.

Regards,

Sokkorn

Re: IF Function field in Set Analysis

Tue, 09 Jul 2013 10:45:46 GMT

Something wrong with syntax.

Thanks for your attension, but we decided to find another way.

Re: IF Function field in Set Analysis

Sokkorn — Tue, 09 Jul 2013 10:47:21 GMT

Hi,

Any sample app? then we can easy to find a new solution.

Rgds,

Sokkorn

Re: IF Function field in Set Analysis

Tue, 09 Jul 2013 10:50:10 GMT

unfortunately, now I have no permission to attach a file

Thank you