Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi fabio,
Can You Please clarify the Query
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.
Hey fabio,
For Which two weeks frame you are asking for avarage..
Pls explain it...
Thanks
Suneel
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.
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..