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

# Gauge Accumulate Data for Previous Months

Hello,

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

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

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

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

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

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

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

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

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

DROP Table Temp;

TempCalendar:

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

MasterCalendar:

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

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