Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
ekelley93
Contributor III
Contributor III

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.

1 Solution

Accepted Solutions
atkinsow
Specialist II
Specialist II

Something like this should work



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

View solution in original post

8 Replies
atkinsow
Specialist II
Specialist II

Something like this should work



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

View solution in original post

ekelley93
Contributor III
Contributor III
Author

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!

atkinsow
Specialist II
Specialist II

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.

ekelley93
Contributor III
Contributor III
Author

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?

ekelley93
Contributor III
Contributor III
Author

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;

ekelley93
Contributor III
Contributor III
Author

Thank you so much for your help!

atkinsow
Specialist II
Specialist II

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

ekelley93
Contributor III
Contributor III
Author

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.