10 Replies Latest reply: May 2, 2017 5:27 AM by Zied Ahmed RSS

    calculate taxe per month

    Zied Ahmed

      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;

          • Re: calculate taxe per month
            Rahul Pawar

            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

              • Re: calculate taxe per month
                Zied Ahmed

                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;

                  • Re: calculate taxe per month
                    Shiva Nagesh Bodepudi

                    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;

                    • Re: calculate taxe per month
                      Rahul Pawar

                      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

                      • Re: calculate taxe per month
                        Zied Ahmed

                        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;

                          • Re: calculate taxe per month
                            Rahul Pawar

                            Hello Zied,

                             

                            Could you please share the screenshot of error message?

                             

                            Regards!

                            Rahul

                              • Re: calculate taxe per month
                                Zied Ahmed

                                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;

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                invalid.png