Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Average column of an expression without invalid dates

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

5 Replies
Highlighted
Creator III
Creator III

Average column of an expression without invalid dates

hi fabio,

Can You Please clarify the Query

Highlighted
Not applicable

Average column of an expression without invalid dates

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.

Highlighted
Creator III
Creator III

Average column of an expression without invalid dates

Hey fabio,

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

Pls explain it...

Thanks

Suneel

Highlighted
Not applicable

Average column of an expression without invalid dates

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.

Highlighted
Creator III
Creator III

Average column of an expression without invalid dates

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..