6 Replies Latest reply: May 31, 2012 5:39 AM by JoaquinLR RSS

Calculate Average Labor Rate

guilherme.caselato

Hello,

 

I have this little issue,

 

I need to calculate the average of the labor rate, but all my entries have different currencies, EUR, USD and etc...

At each entry I have a columm titled currency where it states which currency the order bill was emitted.

I need QlikView to get the entry date, and look for the currency value at that day the bill was emited, so I can sum everything up and calculate the average.

 

So, I need it to multiply each Labor Rate for it's respective currency rate at certain day, and then multiply all entries.

 

Thank you,

Guilherme

  • Re: Calculate Average Labor Rate
    swuehl

    Guilherme,

     

    you would need to describe your data model a bit closer. For example, if you have a currency exchange rates (to a common base currency) on a daily base, a join using Currency and Date Field might all that is needed. If you have time intervals for currency exchange rates, we are looking at so called slowly changing dimensions, and you could try using interval match to link the appropriate exchange rates per currency and date to your labor table. For latter, you could use maybe something like this:

     

    Labor:

    LOAD * INLINE [

    Labor, Value, Currency, Date

    A, 10, EUR, 01.01.2012

    B, 20, EUR, 01.04.2012

    C, 10, USD, 01.01.2012

    D, 20, USD, 01.02.2012

    E, 30, USD, 01.04.2012

    ];

     

    Rates:

    LOAD * INLINE [

    Currency, Rate, FromDate, ToDate

    EUR, 2, 01.01.2012, 31.01.2012

    EUR, 3, 01.02.2012, 31.12.9999

    USD, 1, 01.01.2012, 29.02.2012

    USD, 2, 01.03.2012, 31.12.9999

    ];

     

    Inner join (Labor) IntervalMatch (Date, Currency) LOAD FromDate, ToDate, Currency Resident Rates;

     

    See also attached.

     

    Regards,

    Stefan

    • Re: Calculate Average Labor Rate
      guilherme.caselato

      Hello Stefan, thank you for your help,

       

      so what I have here is a table with daily rates since 2008, so I have the Date, Currency and the Rate for each day and for each currency, and the table comes like this

       

      DateBrazilian real(BRL)euro(EUR)U.S. dollar(USD)
      01/02/081.7705  1.4688 
      01/03/081.7714  1.4753 
      01/04/081.7561  1.4727 
      01/07/081.7566  1.4723 
      01/08/081.7667  1.4705 
      01/09/081.7546  1.468 
      01/10/081.7683  1.4662 
      01/11/081.762  1.4792 
      01/14/081.7528  1.4895 
      01/15/081.4886  1
      01/16/081.7442  1.4792 

      1

       

      I can change this table to something that looks like the one you entered, but only in excel, can I do something like that in QlikView?

       

      And the Labor Rate Table I have the Date, Currency, and the Labor Rate in the currency from the country where the order was issued.

       

      What I need is to calculate everything in USD and then make an average of the Labor Rate.

       

      But I need to a way that this load is automatic because I need to update it every month and the Labor Rate Tables are lots of Excel sheets with lots of rows on each one.

       

      Thank you for your help,

      Guilherme

      • Re: Calculate Average Labor Rate
        swuehl

        Well, if you have exchange rates for every day (at least for every labor record /working day), I think this is making things even more simple (I assuming that the record for EUR at 01/15/08 is accidentily missing).

         

        Your Rates table looks like

         

        Rates:

        LOAD * INLINE [

        Date,          Brazilian real(BRL),          euro(EUR),          U.S. dollar(USD)

        01/02/08,          1.7705,           1.4688,           1

        01/03/08,          1.7714,           1.4753,           1

        01/04/08,          1.7561,           1.4727,           1

        01/07/08,          1.7566,           1.4723,           1

        01/08/08,          1.7667,           1.4705,           1

        01/09/08,          1.7546,           1.468,           1

        01/10/08,          1.7683,           1.4662,           1

        01/11/08,          1.762,           1.4792,           1

        01/14/08,          1.7528,           1.4895,           1

        ] ;

         

        There are multiple ways to assign the correct exchange rate to your labor records, for example using a lookup() function:

         

        Labor:

        LOAD *,

        pick(match(Currency,'BRL','EUR','USD'),

        lookup('Brazilian real(BRL)','Date',LaborDate,'Rates'),lookup('euro(EUR)','Date',LaborDate,'Rates'),1) as Rate;

         

        LOAD * INLINE [

        LaborDate, Value, Currency

        01/03/08, 100, BRL

        01/08/08, 100, EUR

        01/14/08, 100, USD

        ];

         

        The pick() function will pick the correct lookup per Currency. The lookup() will retrieve the correct rate per currency and date (Please refer to the Help for more details).

         

        There is another approach which I would prefer:

         

        CrossRates:

        CrossTable (Currency, Rate) LOAD * resident Rates;

         

        Result:

        LOAD * INLINE [

        LaborDate, Value, Currency

        01/03/08, 100,Brazilian real(BRL)

        01/08/08, 100,euro(EUR)

        01/14/08, 100,U.S. dollar(USD)

        ];

         

        Left Join (Result) Load Date as LaborDate, Currency,Rate Resident CrossRates;

         

        drop table CrossRates;

         

        The Crosstable load transforms your rate table to a straight table format, with fields Date, Currency, Rate. So you can easily join this table to your labor table (also having a date and currency) to retrieve the correct rate.

         

        Hope this helps,

        Stefan

  • Re: Calculate Average Labor Rate
    guilherme.caselato

    Here it is!

     

    FYI:

     

    [Claim Close Date] is the date field

    [Currency] is the currency field

     

     

    end at the end of the script I have the following code:

     

     

    Moedas:

    LOAD Date,

      [BRL],

      [EUR],

      [USD]

    FROM [F:\Rates.xls] (biff, header is line, embedded labels, table is Exchange$);

     

     

    Where: BRL, EUR and USD contains the exchange rate per day listed on Date...

     

     

    The data are like:

    Date          BRL    EUR  USD

    14/05/12     1,8     1,3     1

     

    Thank you so much for your attention

    • Re: Calculate Average Labor Rate
      swuehl

      Well, it is not very complicated to adapt your script to my second approach, is it?

       

      Like

       

      eParts:

      LOAD [F1],

                [Claim #],

                [Work Order #],

                [Operator],

                [Shop Name],

                [Shop Code],

                [Claim Close Date],

                [Arrival Date],

                [Service Complete Date],

                [A/C Flight Hours],

                [A/C Cycle],

                [Currency],

                [Claim Total],

                [Total Approved],

                [Expendable Parts],

                [Total Handling Fee],

                [Total Freight],

                [Total Miscellaneous],

                [Labor Rate],

                [Labor History],

                [Approved? Y / N],

                [Comment],

                [Classification],

                [Total Amount],

                IF([Claim #]=PREVIOUS([Claim #]),'0','1') as UNIQUE_FLAG

       

      FROM [F:\Eparts_*.xls] (biff, header is line, embedded labels, table is Report$);

       

      LOAD [Shop Name],

                 [ServiceName],

                 [ServiceType],

                 [Region]

      FROM [F:\Service.xls] (biff, embedded labels, table is Sheet1$);

       

      Moedas:

      CROSSTABLE (Currency, Rate) LOAD Date as [Claim Close Date],

                 [BRL],

                 [EUR],

                 [USD]

      FROM [F:\Rates.xls] (biff, header is line, embedded labels, table is Exchange$);

       

      LEFT JOIN (eParts) LOAD * resident Moedas;

       

      drop table Moedas;

       

      To make this work, your Currency values in your eParts table must match the field names of your Moedas excel file (to match in the join).

      • Re: Calculate Average Labor Rate
        JoaquinLR

        Fantastic Stefan and Guilherme.

         

        I'm new in QV and I need a little variation, what would the correct sentence CROSSTABLE for this format of the table MOEDAS

         

        Moedas:

        LOAD Start_Date, End_Date

          [BRL],

          [EUR],

          [USD]

        FROM [F:\Rates.xls] (biff, header is line, embedded labels, table is Exchange$);