topic Re: No Data to Display when using Avg() in QlikView

No Data to Display when using Avg()

Thu, 20 Mar 2014 05:32:54 GMT

Hi:

I have a variable called vTotalTenure.

I developed this variable base on a pivot table with DateDimension on top and RowID on the right (Both are unique).

Basically it is a difference between two dates and divided by 365. The selection of the dates are base on some IF functions.

Some of them are numbers and some of them are NULL().

I add vTotalTenure in the expression as =$(vTotalTenure)

And then I am trying to calculate the average of vTotalTenure in a line chart with DateDimension at the bottom.

But when I typed in the following in the expression, it said 'No data to display':

=Avg($(vTotalTenure))

Can anyone help me with this?

er_mohit — Thu, 20 Mar 2014 05:40:47 GMT

Try this

'$(=Avg(vTotalTenure))'

or this

=Avg('$(vTotalTenure)')

=Avg('$(=vTotalTenure)')

jonathandienst — Thu, 20 Mar 2014 05:43:44 GMT

Does the definition of vTotalTenure contain any aggregation functions (such as Sum, Count, Avg or stats aggregate functions)?

If so, you will need

+Avg(Aggr($(vTotalTenure), dim1, dim2...))

Where dim1,... represents all the dimensions used in your chart/table.

HTH

Jonathan

Thu, 20 Mar 2014 05:48:49 GMT

Thank you for your reply

But sorry, it seems like they don't work.

Thu, 20 Mar 2014 05:51:20 GMT

Hi Jonathan:

Thanks for your advice but I don't have any aggregation functions.

Thu, 20 Mar 2014 05:55:06 GMT

This is what's in my vTotalTenure variable:

(IF(IDNumber_ST<>NextIDNumber_ST,

IF(Status_ST='A',

IF(MonthEnd(Dimension_HC)>HireDate_ST,MonthEnd(Dimension_HC)-HireDate_ST,NULL()),

NULL())

,NULL())

/365

)

Maybe this would help you understand my question.