topic Average column of an expression without invalid dates in QlikView

Average column of an expression without invalid dates

Wed, 14 Jul 2010 11:15:33 GMT

Hi everyone,

I have a "simple" questionfor you all:

I have to calculate the average value for an expression (let's call it "budget") on a time frame.

I have a calendar dimension table, from 01-01-2009 to 12-31-2011, which has to be fully loaded into the dashboard. The problem comes because I have budget values from 07-01-2010 to today. The average, therefore, is not calculated in this 2 weeks frame, but in the whole calendar frame.

How can I fix this? Is it possible to add a column for the average calculated with an expression?

I hope to have been clear.

Thanks a lot!

Fabio

Average column of an expression without invalid dates

sunil2288 — Wed, 14 Jul 2010 11:31:09 GMT

hi fabio,

Can You Please clarify the Query

Average column of an expression without invalid dates

Wed, 14 Jul 2010 11:42:02 GMT

Hi Sunil,

here is the query to populate the QVDs.

d_calendario:
select calendario_id, calendario_giorno as cal_giorno, calendario_dd as cal_dd, calendario_mm as cal_mm, calendario_yyyy as cal_yyyy, calendario_nomemese as cal_nomemese, upper(left(calendario_giornosettimana,3)) as cal_giornosettimana, calendario_giornosettimananumero as cal_giornosettimananumero from dim_calendario where year(calendario_giorno)<=year(getdate())+1 order by calendario_giorno;
Store d_calendario into [qvd\assenteismo\d_calendario.qvd];

f_turnistica:
SELECT
calendario_id,
turno_presenza_teorica,
turno_permesso,
turno_strao,
CASE WHEN (giustificativo_macro IS NULL) THEN 'PRESENTE' ELSE giustificativo_macro END giustificativo_macro,
CASE WHEN (giustificativo_testata IS NULL) THEN 'PRESENTE' ELSE giustificativo_testata END giustificativo_testata,
CASE WHEN (giustificativo_dettaglio IS NULL) THEN 'PRESENTE'
WHEN (giustificativo_dettaglio = 'SUPPORTO BACKOFFICE') THEN 'PRESENTE'
WHEN (giustificativo_dettaglio = 'ALTRO') THEN 'PRESENTE'
ELSE giustificativo_dettaglio
END giustificativo_dettaglio,
turno_activity,
turno_contratto,
turno_centro
FROM
fc_turnistica
INNER JOIN dim_calendario ON fc_turnistica.turno_calendario = dim_calendario.calendario_id
LEFT OUTER JOIN dim_giustificativi ON dim_giustificativi.giustificativo_id=fc_turnistica.turno_giustificativo
WHERE turno_activity<>'Churn' and turno_activity<>'POLO BKL';

Store f_turnistica into [qvd\assenteismo\f_turnistica.qvd];

the d_calendario is the dimension for the dates.

Average column of an expression without invalid dates

sunil2288 — Wed, 14 Jul 2010 12:00:05 GMT

Hey fabio,

For Which two weeks frame you are asking for avarage..

Pls explain it...

Thanks

Suneel

Average column of an expression without invalid dates

Wed, 14 Jul 2010 12:19:49 GMT

Sorry Suneel, i will explain, i also attach the file i'm developing.

You can see, in the main table "Trend assenteismo" the last column "EOM", if there are no selections the average is wrong, because I want it to be calculated only for the values i have on the table.

Average column of an expression without invalid dates

sunil2288 — Thu, 15 Jul 2010 10:19:18 GMT

Hi Fabio,

I saw your application .

In your application you just go to the trend assentismo properties then to number tab and Number format settings make it as Expression default..

Thanks..