topic Re: error in set modifier expression for avg expression with clause in QlikView

error in set modifier expression for avg expression with clause

matthewp — Tue, 21 Feb 2017 10:13:38 GMT

My expression will not work and i am getting the error "error in set modifier expression", it is only a simple expression but i cant understand why it wont work.

avg({<NOT ISNULL([table.ApproveDate])>} table.days)

Re: error in set modifier expression for avg expression with clause

kaushiknsolanki — Tue, 21 Feb 2017 10:24:08 GMT

HI try this,

=avg({<Product -= {"$(=isnull(table.ApproveDate))"}>}tables.days)

Regards,

Kaushik Solanki

Re: error in set modifier expression for avg expression with clause

sunny_talwar — Tue, 21 Feb 2017 10:28:13 GMT

May be this

Avg({<[table.ApproveDate] = {'*'}>} table.days)

Re: error in set modifier expression for avg expression with clause

sunny_talwar — Tue, 21 Feb 2017 10:29:04 GMT

or this

Avg(If(Len(Trim([table.ApproveDate])) > 0, table.days))

Re: error in set modifier expression for avg expression with clause

matthewp — Tue, 21 Feb 2017 11:08:03 GMT

This works perfect!

Side question: how can i do the opposite to Avg({<[table.ApproveDate] = {'*'}>} table.days) as in NULL rather than NOT NULL

Re: error in set modifier expression for avg expression with clause

sunny_talwar — Tue, 21 Feb 2017 11:10:39 GMT

This becomes a slightly complicated because of the fact that you are looking for NULL. To do this, I would use a uniqueIdentifier field like this

Avg({<UniqueIdentifier = {"=Len(Trim([table.ApproveDate])) = 0">} table.days)

Re: error in set modifier expression for avg expression with clause

matthewp — Tue, 21 Feb 2017 11:12:30 GMT

What is UniqueIdentifier ?

Also I am getting the error "Error in set modifier ad hoc element list"

Re: error in set modifier expression for avg expression with clause

sunny_talwar — Tue, 21 Feb 2017 11:15:08 GMT

UniqueIdentifier is a placeholder for a field which Uniquely identify each of the row of your table. T

Re: error in set modifier expression for avg expression with clause

matthewp — Tue, 21 Feb 2017 11:18:01 GMT

Ok so using:

Avg({<table.IDNumber= {"=Len(Trim([table.ApproveDate])) = 0">} table.days)

I am still getting the error "Error in set modifier ad hoc element list" even though nothing is underlined in red

Re: error in set modifier expression for avg expression with clause

matthewp — Tue, 21 Feb 2017 11:23:36 GMT

Solved this without set analysis:

=SUM(if(IsNull(table.ApproveDate), table.days))/COUNT(if(IsNull(table.ApproveDate), table.days))

Re: error in set modifier expression for avg expression with clause

sunny_talwar — Tue, 21 Feb 2017 12:12:34 GMT

I would suggest to create a new field in the script like this

LOAD table.ApproveDate,

If(Len(Trim(table.ApproveDate)) = 0, 1, 0) as NullFlag

Now you can try like this:

Null

Avg({<NullFlag = {1}>} table.days)

Not Null

Avg({<NullFlag = {0}>} table.days)