Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlik_j
Contributor
Contributor

Current Year Max Month vs Last Year Same Month

Hi All, 

Need your help on this one. 

I am trying to add a KPI that shows Employee count for current year max month (my data is updated to Feb 2020) and then add a second KPI that shows Last year same month (Feb 2019).

For current year max month I am trying to usethe following but it gives me 0:

sum({<DateofReport={"=Num(DateofReport)=$(=Max(DateofReport))"}, Leave -= {"1"} >} [TOTAL_FTE])

DateofReport is in the format  MM/DD/YYYY

In addition, how do I calculate last year same month (Feb 2019)?  Also I cannot use "today" since my data is updated till Feb 2020.

I also have this in my script if it can be useful?

MaxTable:
Load Max([DateofReport]) As MaxDate
Resident FTEtemp;

let vMaxReportDate = Peek('MaxDate');

Your help will be highly appreciated...

Thank you all in advance

 

Labels (3)
1 Reply
kashyap
Partner
Partner

Hi

Try this

Current Month Current Year

Sum({<[Date]={">=$(=MonthStart(max([Date])))<=$(=Date(Max([Date])))"}>} Value)

Current Month Last Year

Sum({<[Date]={">=$(=MonthStart(AddYears(max([Date]),-1)))<=$(=Date(AddYears(Max([Date]),-1)))"},Month,Year>} Value)

Hope this helps

Thanks