# Qlik Sense Deployment & Management

Contributor III

## calculate taxe per month

Hello,

I have this code and i need to calculate the Taxe per Month:

NoConcatenate

Table2:

Plaque,

date,

Materiel,

Plaque as Plaque,

sum(statut_endom) as statut_endom,

sum(statut_actif)  as statut_actif,

sum(statut_inactif)  as statut_inactif,

sum(statut_bloque)  as statut_bloque,

(sum(statut_actif)/day(date)) as Taxe

resident Table1

Group by date_flotte,Materiel_flotte,Plaque_flotte,Plaque;

drop table Table1;

10 Replies
Contributor III

## Re: calculate taxe per month

Valued Contributor III

## Re: calculate taxe per month

Hello Zied,

Below are two probable solutions:

1. Script Level

```TaxePerMonth:
Sum(Taxe) AS MonthlyTaxe
Resident Table2
Group By Month(date);
```

2. Chart Level

-Create a month dimension by adding calculated dimension as - Month(date)

Regards!

Rahul

Contributor III

## Re: calculate taxe per month

Hello Rahul,

When I do this I have invalide expression...

Table2:

Plaque,

Month(date) as Month_date_flotte,

Materiel_flotte,

Plaque_flotte as Plaque_flotte,

sum(statut_endom) as statut_endom,

sum(statut_actif)  as statut_actif,

sum(statut_inactif)  as statut_inactif,

sum(statut_bloque)  as statut_bloque,

(sum(statut_actif)/day(date_flotte)) as Taux

resident Table1

Group by  Month(date_flotte),Materiel_flotte,Plaque_flotte,Plaque;

drop table Table1;

Valued Contributor

## Re: calculate taxe per month

Think you should do this

Plaque,

Month(date_flotte) as Month_date_flotte,

Materiel_flotte,

Plaque_flotte as Plaque_flotte,

sum(statut_endom) as statut_endom,

sum(statut_actif)  as statut_actif,

sum(statut_inactif)  as statut_inactif,

sum(statut_bloque)  as statut_bloque,

(sum(statut_actif)/day(date_flotte)) as Taux

resident Table1

Group by  Month(date_flotte),Materiel_flotte,Plaque_flotte,Plaque;

drop table Table1;

Valued Contributor III

## Re: calculate taxe per month

Hello Zied,

You are referring two different field names in select list and Group By field list.

//Here you are referring date field

Month(date) as Month_date_flotte

//Whereas, here you are referring date_flotte field

Group by  Month(date_flotte)

At both the places refer same field to get the desired results.

Regards!

Rahul

Contributor III

## Re: calculate taxe per month

Hello,

Yes i just copied it this is the code and gives me invalid expression:

NoConcatenate

Table2:

Plaque,

Month(date_flotte) as Month_date_flotte,

Materiel_flotte,

Plaque_flotte as Plaque_flotte,

sum(statut_endom) as statut_endom,

sum(statut_actif)  as statut_actif,

sum(statut_inactif)  as statut_inactif,

sum(statut_bloque)  as statut_bloque,

(sum(statut_actif)/day(date_flotte)) as Taxe

resident Table1

Group by  Month(date_flotte),Materiel_flotte,Plaque_flotte,Plaque;

drop table Table1;

Valued Contributor III

## Re: calculate taxe per month

Hello Zied,

Could you please share the screenshot of error message?

Regards!

Rahul

Contributor III

## Re: calculate taxe per month

//2 éme vue qui contient le statut de chaque remorque par jour

Flotte:

date(Today()) as date_flotte,

TrailerType as Materiel_flotte,

ParkName as Parc_flotte,

SATUT as stat_flotte;

SELECT Plate,

TrailerType,

ParkName,

SATUT

FROM x.dbo."CG_FLOTTE" ;

NoConcatenate

Plaque_all:

'1' as statut_inactif,

Type_Materiel as Materiel_flotte,

date(Today()) as date_flotte

resident Fact_Final Where NOT Exists(Plaque_flotte,Plaque) and Type_Materiel<>'DiversMateriel';

Concatenate

Flotte1:

Plaque_flotte ,

Materiel_flotte,

date_flotte,

if(stat_flotte='ACTIF',1,0) as statut_actif,

if(stat_flotte='INACTIF',1,0)  as statut_endom,

if(stat_flotte='BLOQUE',1,0)  as statut_bloque

Resident Flotte;

drop table Flotte;

NoConcatenate

Flotte2:

Plaque,

Month(date_flotte) as Month_date_flotte,

Materiel_flotte,

Plaque_flotte as Plaque_flotte,

sum(statut_endom) as statut_endom,

sum(statut_actif)  as statut_actif,

sum(statut_inactif)  as statut_inactif,

sum(statut_bloque)  as statut_bloque,

(sum(statut_actif)/day(date_flotte)) as Taux

resident Plaque_all

Group by  Plaque,Month(date_flotte),Materiel_flotte,Plaque_flotte;

drop table Plaque_all;

//Condition pour savoir le statut du remorque

NoConcatenate

Flotte3:

Taux,

Plaque,

statut_inactif,

statut_bloque,

statut_endom,

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

if (statut_actif>statut_bloque and statut_actif>statut_inactif,1,

(if(statut_bloque>statut_actif and statut_bloque>statut_inactif,2,

(IF(statut_inactif>statut_actif and statut_inactif>statut_bloque,3))))) as Statut_cond,

statut_actif

Resident Flotte2;

drop table Flotte2;

NoConcatenate

Flotte4:

Plaque,

statut_actif,

date_flotte,

statut_inactif,

statut_bloque,

statut_endom,

//Statut_sup,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

Taux,

if (Statut_cond=1,'Actif',

(if (Statut_cond=2,'Bloquée',

(IF(Statut_cond=3,'Inactif'))))) as Statut_sup

Resident Flotte3;

drop table Flotte3;

//pour SR sans traversée

NoConcatenate

FlotteFinal:

// autonumber( date(date_dossier, 'DD/MM/YYYY'),'%Date ID')  as [%Date ID],

date_flotte,

Plaque_flotte,

Materiel_flotte,

Taux,

Plaque  FROM [\$(vQvdPath)\Vectorys_final.qvd] (qvd);//source des QVDs historisés

Concatenate

//Concatenation du QVD avec les données d'ajourd'hui

* WHERE  "date_flotte"= Today();

STORE [FlotteFinal] into [\$(vQvdPath)\Qvd_today.qvd](qvd);

//Plaque,

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

Taux,

'ON'  as STATUT,

count(Statut_sup) as nbr

resident Flotte4

where Statut_sup='Actif'

group by

date_flotte,

Taux,

//[%Date ID],

Plaque_flotte,

Materiel_flotte;

Concatenate

Plaque,

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

'OFF'  as STATUT,

count(Statut_sup) as nbr

resident Flotte4

where Statut_sup='Inactif'

group by  date_flotte,

Plaque_flotte,

Materiel_flotte,Plaque;

Concatenate

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

'Bloquée'  as STATUT,

count(Statut_sup) as nbr

resident Flotte4

where Statut_sup='Bloquée'

group by  date_flotte,

Plaque_flotte,

Materiel_flotte;

drop table Flotte4;

STORE [FlotteFinal] into [\$(vQvdPath)\QVD_final.qvd](qvd);

Concatenate

resident Fact_Final ;

drop table Fact_Final;

Valued Contributor

## Re: calculate taxe per month

You should have day(date_flotte) in group by clause. If you need to calculate sum(statut_actif)/day(date_flotte).

But that will not give aggregated values at month level.

What exactly you are trying to do here.