8 Replies Latest reply: Nov 27, 2017 11:17 AM by Erin Kelley RSS

    Gauge Accumulate Data for Previous Months

    Erin Kelley



      I am creating a gauge in my Qlikview dashboard to track how close we are to meeting our financial goal at the end of the year.


      Our goal is to reach $8,000,000 by the end of December 2017. Each financial figure is kept track of monthly and continually is added to the previous month to track our financials. I would like to have the gauge correlate with the month fields that I have listed.


      Right now, when I select for instance "Feb", the gauge only calculated the financials for Februrary, however I would like to track Jan-Feb when I select "Feb." Is there a formula that can be used so that the month selected can also track the previous months as well?


      Here is the formula I have currently:

      =Sum([Financial Impact])/8000000


      Here is an example of what I have currently: (when I select February it just shows the % for February, not January and February which I would like)


      If this is not possible, the only workaround I have figured is to select both January and February.

        • Re: Gauge Accumulate Data for Previous Months
          Wallo Atkinson

          Something like this should work

          Sum({<DateField={">=$(=YearStart(Max(DateField),0,1))<$(=MonthEnd(Max(DateField)))"}>}[Financial Impact])/8000000

            • Re: Gauge Accumulate Data for Previous Months
              Erin Kelley

              Hmm..I tried that expression but there were errors in the formula. I am pretty new to qlikview, so maybe explaining what your expression means would be helpful! Thank you!

                • Re: Gauge Accumulate Data for Previous Months
                  Wallo Atkinson

                  Uses section access to specify a date range.


                  DateField would be whatever date field you have in your data.  Could be [Accounting Date], [Billed Date] or whatever.  But it would have to be a date field.  If you don't have one, you should probably create one in your script.

                  If your data is at the month level only, add a date field where the date is the first day of the month.

                  The set analysis is saying pull the data from the beginning of the calendar year through the end of the available month derived from your date field.

                    • Re: Gauge Accumulate Data for Previous Months
                      Erin Kelley

                      Ok, thank you for explaining.


                      Below is a screenshot of my script. So yes, the data is year and month level only. How would I go about adding a date field to the script?


                        • Re: Gauge Accumulate Data for Previous Months
                          Erin Kelley

                          I loaded a calendar in my script below: Now I am thinking what might be best is to have a Month to Date Field added. Similar to how the Quarter is.


                          Quarter Field ---->


                          I would like the same look of the "quarter" for month to day, if possible


                          MAPPING LOAD
                          rowno() as Month,
                          'Q' &
                          Ceil (rowno()/3) as Quarter
                          AutoGenerate (12);

                          min(DischargeDTS) as minDate,
                          max(DischargeDTS) as maxDate

                          Resident DischargeHeader;

                          Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
                          Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                          DROP Table Temp;



                          $(varMinDate) + Iterno()-1 As Num,
                          Date($(varMinDate) + IterNo() - 1) as TempDate
                          AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);


                          TempDate AS DischargeDTS,
                          week(TempDate) As Week,
                          Year(TempDate) As Year,
                          Month(TempDate) As Month,
                          Day(TempDate) As Day,
                          YeartoDate(TempDate)*-1 as CurYTDFlag,
                          YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
                          inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
                          date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
                          ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
                          If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,
                          Week(TempDate) & '-' & Year(TempDate) as WeekYear,
                          WeekDay(TempDate) as WeekDay

                          Resident TempCalendar

                          Order By TempDate ASC;

                          Drop Table TempCalendar;

                            • Re: Gauge Accumulate Data for Previous Months
                              Wallo Atkinson

                              I thought you didn't have a date field in your data?  What does your calendar link to?

                              There's nothing wrong with a calendar, but I would normally make it to the level that your data is at.

                              So if you only have months, make your calendar's most detailed level, Month.  That's my opinion.


                              to  make your month and year field a date field with the first day of the month your would do something like


                              date(makedate([YourYearField],[YourMonthField],1),'M/D/YYYY') as MonthDate

                      • Re: Gauge Accumulate Data for Previous Months
                        Erin Kelley

                        Thank you so much for your help!