8 Replies Latest reply: Feb 7, 2012 8:28 AM by Ben Reed RSS

    Create Coumns for months

    Ben Reed

          I have data in a format with a cost per day, cost start, and cost finish date for every record.  What i would like to do is create a new column for each month that the cost occurs in with a total cost for those months based on a cost per day. I would like to do this on the load if possible.  Any help would be appreciated.

       

      For instance   the current data format is this:

      SERIAL_NOCS_DATEAVG_TATCF_DATECOST_PER_DAY
      A-31701/10/2012382/17/2012$7,891.35
      A-249102/02/2012383/11/2012$7,891.35

       

      I would like to have this for the same two records

      SERIAL_NOCS_DATEAVG_TATCF_DATECOST_PER_DAYJanuaryFebruaryMarchtotal cost
      A-31701/10/2012382/17/2012$7,891.35$    165,718.36 $134,152.96 $             -   $299,871.32
      A-249102/02/2012383/11/2012$7,891.35$                 -   $213,066.47 $86,804.86 $299,871.32
        • Create Coumns for months
          Karl Pover

          A QV best practice would be to create a month column,

           

          month(CS_DATE) as Month

           

          and create the cross table on in the QV interface which is really simple.

           

          Otherwise, you could try in the script to make a join between the detailed table with an aggregated table,

           

          Detail:

          Load SERIAL_NO,

               CS_DATE,

               AVG_TAT,

               CF_DATE,

               MONTH(CF_DATE) AS Month,

               COST_PER_DAY

          From ...;

           

          Left Join

          Load Month,

               sum(if(Month = 1,COST_PER_DAY,0)) as January,

               sum(if(Month = 2,COST_PER_DAY,0)) as February,

               ...

          Resident Detail

          Group by Month;

           

           

          Karl

            • Create Coumns for months
              Ben Reed

              Thanks for that Karl, however I don't think this is performing the calculation as needed.  What i need to do is multiply the cost per day by the amount of days in the month where the cost will be incurred. 

               

              For example on "serial No" A-317 above the value in the "January" column will be Cost per day * (last day in january - cost start date)

              so effectively it will be $7,891.35*(31-10) = $165,718.35

               

              I have it working in Excell but i am limited there and would like to pull this into Qlikview to simplify the process and expand on my data.

               

              any advice?

                • Create Coumns for months
                  Karl Pover

                  Are you looking for something like this,

                   

                  Detail:

                  Load SERIAL_NO,

                       CS_DATE,

                       AVG_TAT,

                       CF_DATE,

                       COST_PER_DAY,

                       if(month(CS_DATE)=1, COST_PER_DAY * (day(monthend(CS_DATE)) - day(CS_DATE))) as January,

                       ...

                  From ...;

                   

                  Karl

                    • Create Coumns for months
                      Ben Reed

                      That is the beginnings of it Karl... you are a saint.

                       

                      I don't have experience with loops but i think this would be a perfect place to put a loop.  What i need to do is create a column for each YYYY_MM in my record set.  so rather than having to type the if statement fore each YYYY_MM can't i do a loop that would go through all the records and populate that fore me?

                       

                      so really i would have multiple January columns.  I can restrict the script to only look 18 months out to keep it from having to work too hard.  IF i restrict it to 18 months the most i would have is two of any month column.

                        • Re: Create Coumns for months
                          Karl Pover

                          Here's an idea using a loop.  You'll have to create an empty table that we will concatenate to in the loop.  Then in the loop we're going to concatenate each of the 18 past months to the empty table, and each month will create a new column that corresponds to the month and year of the CS_DATE.  This solution does not consider having information older than 18 months in the table. 

                           

                          Note: This isn't tested so try it out and if you have any more questions, please ask.

                           

                          Also, I don't know how you are going to present this in the interface.  Having variable column names doesn't give you the easist way to build and maintain the QV interface, so it's worth noting that what you are calculating can also be done with a sum(if(...)) in the interface without creating these additional columns.

                           

                          Costs:

                          Load null() as SERIAL_NO,

                               null() as CS_DATE,

                               null() as AVG_TAT,

                               null() as CF_DATE,

                               null() as COST_PER_DAY

                          Autogenerate 1;

                           

                          for vCounter = 0 to 17

                           

                               set vMonthName = monthname(today(),-vCounter);

                               set vMonthYear_ColumnName = text(monthname(today(),-vCounter));

                           

                               Concatenate(Costs)

                               Load SERIAL_NO,

                                    CS_DATE,

                                    AVG_TAT,

                                    CF_DATE,

                                    COST_PER_DAY,

                                    COST_PER_DAY * (day(monthend(CS_DATE)) - day(CS_DATE)) as [$(MonthYear_ColumnName)]

                               From ...

                               Where monthname(CS_DATE) = $(vMonthName);

                           

                          next

                           

                           

                           

                          Karl

                            • Re: Create Coumns for months
                              Ben Reed

                              Hi Karl,

                              Thanks for all the help so far.  I have been tinkering with the script and have come up with the following which is working to calculate the current month cost by multiplying the cost per day by the amount of days the work is to be forecasted for the current month with multiple IF statements. What I am trying to do is to forecast 18 months into the future.  This is contrary to the loop examply you sent which is pulling historical data.  I am wondering if there is a better method of doing this than the script i have below.  Also what i would like to do is turn the IF statements below into a loop which would perform the same calculation for the next 17 months and name the column for that month.  Any help is appreciated.

                              LET

                               

                              $(fileyear)

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                                • Create Coumns for months
                                  Ben Reed

                                  sorry, the script will not paste

                                  here is a image of the scriptforecast QVD.jpg

                                    • Re: Create Coumns for months
                                      Ben Reed

                                       

                                      I have attached the script that i have deveolped so far.  What this does is sets a month value for each month from today until 18 months from now in the following format (YYYY_MM)

                                       

                                      these values are then used to calculate the total $ in cost that will occur for each month on each record in the load.  That value is placed in a new colum named for the month as outlined above (YYYY_MM).  As Carl stated above this has limited how i can use the data in reporting and i would rather have all $ values in a single column labled M onthly Cost and another column labeld Month that would indicate which month these dollars will occur in.   I am certain this can be simplified and done in a loop however i am not familiar with loops and I have surely overcomplicated this. 

                                       

                                      what i currently have is with the attached script in its simplist form is:

                                      Part NumberSerial NumberCost Start DateCost Finish Datecost per daytotal cost2012_022012_032012_042012_05
                                      XY2/2/20124/4/2012$            1.00 $          88.00 $    27.00 $    31.00 $    30.00 $           -  

                                       

                                      what i would like to have for the same data would be :

                                      Part NumberSerial NumberCost Start DateCost Finish Datecost per daytotal costMonthCost
                                      XY2/2/20124/4/2012$            1.00 $    88.00 2012_02$    27.00
                                      XY2/2/20124/4/2012$            1.00 $    88.00 2012_03$    31.00
                                      XY2/2/20124/4/2012$            1.00 $    88.00 2012_04$    30.00