calculate taxe per month

[zied_ahmed1]

Hello,

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

NoConcatenate

    Table2:

    load

          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;

[zied_ahmed1]

rahulpawarb‌

[rahulpawarb]

Hello Zied,

Below are two probable solutions:

1. Script Level

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

2. Chart Level

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

-Add a new expression Sum(Taxe)

Hope this will be helpful.

Regards!

Rahul

[zied_ahmed1]

Hello Rahul,

When I do this I have invalide expression...

  Table2:

    load

          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;

[Anonymous]

Think you should do this

  load

          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;

[rahulpawarb]

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

[zied_ahmed1]

Hello,

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

NoConcatenate

    Table2:

    load

          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;

[rahulpawarb]

Hello Zied,

Could you please share the screenshot of error message?

Regards!

Rahul

[zied_ahmed1]

SET vQvdPath=C:\Users\administrateur.\Desktop\Zied\qvdtest\New; //Path

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

Flotte:

       LOAD Plate as Plaque_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:

       load Distinct Plaque,

       '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:

Load

   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:

    load

          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:

    load

    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:

    load

    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:

     LOAD

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

     LOAD

    * WHERE  "date_flotte"= Today();

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

   load

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

      load

      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 

           load

       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

   Load*

   resident Fact_Final ;

   drop table Fact_Final;

[Anonymous]

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.