Gauge Accumulate Data for Previous Months

[Anonymous]

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.

[Anonymous · Accepted solution]

Something like this should work

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

[Anonymous]

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!

[Anonymous]

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.

[Anonymous]

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?

[Anonymous]

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:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AutoGenerate (12);

Temp:
Load
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;



TempCalendar:

LOAD

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



MasterCalendar:

Load
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;

[Anonymous]

Thank you so much for your help!

[Anonymous]

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

[Anonymous]

I ended up adding a date field (M/D/YYY) in my excel spreadsheet with my other data. I already had month and year, but added dates as well so the calendar would link to the data from the spreadsheet.