5 Replies Latest reply: Feb 20, 2015 9:45 AM by Manish Kachhia RSS

    Split cost by year


      Hi ,

       

       

      i have some project related data , i intend to show the budget in each year ,

       

      if the start date(MM-DD-YYYY) is : 01/04/2014

      and the end date is: 04/04/2015

      budget is 16000

      then the duration is 16 months

       

       

      average budget per month :1000

      budget for year 2014:12000/-

      budget for year 2015:4000/-

       

      how can this been done through Qlikview expressions , i need to show a pie for total budget and then when a year is selected , the budget in that year

       

      now i intend to show the budget  year wise

        • Re: Split cost by year
          Manish Kachhia

          use below script

          =======================

           

          SET ThousandSep=',';

          SET DecimalSep='.';

          SET MoneyThousandSep=',';

          SET MoneyDecimalSep='.';

          SET MoneyFormat='£#,##0.00;-£#,##0.00';

          SET TimeFormat='hh:mm:ss';

          SET DateFormat='MM/DD/YYYY';

          SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

          SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

          SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

           

           

          Date:

          Load * Inline

          [

            Date

            01/04/2014

            04/04/2015

          ];

           

           

          Budget:

          Load * Inline

          [

            Budget

            16000

          ];

           

           

          Temp:

          Load

            Min(Date) as MinDate,

            Max(Date) as MaxDate

          Resident Date;

           

           

          Let vMinDate = NUM(PEEK('MinDate',0,'Temp'));

          Let vMaxDate = NUM(PEEK('MaxDate',0,'Temp'));

           

           

          Calendar:

          Load *, AutoNumber(YearMonth,'YearMonth') as YearMonthID;

          Load

            Date(TempDate) as Date,

            Month(TempDate) as Month,

            Date(MonthStart(TempDate),'YYYYMM') as YearMonth,

            Year(TempDate) as Year;

          Load

            $(vMinDate) + IterNo() - 1 as TempDate

          AutoGenerate 1

          While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

           

           

          MaxYearMonthID:

          Load Max(YearMonthID) as MaxMonthCount Resident Calendar;

           

           

          Drop Table Temp;

           

           

          Let vBudget = Num(Peek('Budget',0,'Budget'));

          Let vYearMonthCount = Num(Peek('MaxMonthCount',0,'MaxYearMonthID'));

           

           

          Let vMonthlyAmount = $(vBudget)/$(vYearMonthCount);

           

           

          FinalTable:

          Load

            Date,

            Month,

            YearMonth,

            Year,

            YearMonthID,

            $(vMonthlyAmount) as MonthlyBudget

          Resident Calendar;

           

           

          Drop Table Calendar;

           

           

          Drop Field YearMonthID;

          ===========================

          Now you can use SUM(MontlyBudget) as expression...

            • Re: Split cost by year

              Hi Manish,

               

              when i am plotting  a pie chart with year as dimension and doing sum(MonthlyBudget) the value is not showing correctly

               

              as in 12000 for 2014

              and 4000 for 2015

               

              on viewing the table viewer i realise the place we are going wrong

              when the budget is being split by day for every day we  have the amount 1000 corresponding to it while it should be 1000 divided by no of days in that month

               

              would be really helpful if you can guide me through

              thanks a lot

              really appreciate the help

            • Re: Split cost by year
              Manish Kachhia

              SET ThousandSep=',';

              SET DecimalSep='.';

              SET MoneyThousandSep=',';

              SET MoneyDecimalSep='.';

              SET MoneyFormat='£#,##0.00;-£#,##0.00';

              SET TimeFormat='hh:mm:ss';

              SET DateFormat='MM/DD/YYYY';

              SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

              SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

              SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

               

               

              Date:

              Load * Inline

              [

                Date

                01/04/2014

                04/04/2015

              ];

               

               

              Budget:

              Load * Inline

              [

                Budget

                16000

              ];

               

               

              Temp:

              Load

                Min(Date) as MinDate,

                Max(Date) as MaxDate

              Resident Date;

               

               

              Let vMinDate = NUM(PEEK('MinDate',0,'Temp'));

              Let vMaxDate = NUM(PEEK('MaxDate',0,'Temp'));

               

               

              Calendar:

              Load *, AutoNumber(Date,'YearMonth') as DateID;

              Load

                Date(TempDate) as Date,

                Month(TempDate) as Month,

                Date(MonthStart(TempDate),'YYYYMM') as YearMonth,

                Year(TempDate) as Year;

              Load

                $(vMinDate) + IterNo() - 1 as TempDate

              AutoGenerate 1

              While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

               

               

              MaxDateCount:

              Load Max(DateID) as MaxDateCount Resident Calendar;

               

               

              Drop Table Temp;

               

               

              Let vBudget = Num(Peek('Budget',0,'Budget'));

              Let vDayCount = Num(Peek('MaxDateCount',0,'MaxDateCount'));

               

               

              Let vDailyAmount = $(vBudget)/$(vDayCount);

               

               

              FinalTable:

              Load

                Date,

                Month,

                YearMonth,

                Year,

                DateID,

                '$(vDayCount)' as DailyBudget

              Resident Calendar;

               

               

              Drop Table Calendar;