topic Re: max year rolling 12 months in QlikView

max year rolling 12 months

everest226 — Mon, 23 Jan 2017 23:30:42 GMT

I am using set Analysis trying to get the data of 12 months rolling but till last months only ,

my code

Count (DISTINCT {<Year={'$(=max(Year))'}, Month={"$(=month(date#(max({<Year={'$(=max(Year))'}>}Month)-1,'MM')))"}>} [user])

Re: max year rolling 12 months

sunny_talwar — Tue, 24 Jan 2017 02:07:41 GMT

Do you have a MonthYear field? If you don't, then try like this

LOAD Date,

Date(MonthStart(Date), 'MMM-YYYY') as MonthYear

....

FROM ....

and then an expression like this:

Count (DISTINCT {<Year, Month, MonthYear = {"$(='<' & Date(Max(MonthYear),'MMM-YYYY'))"}>} [user])

Re: max year rolling 12 months

everest226 — Tue, 24 Jan 2017 17:31:03 GMT

can we work around on this expression for same outcome also?

so the data will be 12 months rolling till last months

Count({<Year = {2017}>} DISTINCT [Authenticated user])

i have these on my script

LOAD CalendarTimeStamp,

Timestamp(CalendarTimeStamp, 'M/D/YYYY h:mm TT') AS [Date Time],

Date(MonthStart(Date(CalendarTimeStamp)), 'YYYY-MMM') AS [YYYY-MMM],

Day(CalendarTimeStamp) AS Day,

Day(CalendarTimeStamp) & ' ' & WeekDay(CalendarTimeStamp) AS [Day-Week],

Week(CalendarTimeStamp) AS Week,

Month(CalendarTimeStamp) AS Month,

RowCount

FROM MinuteCalendar.qvd (qvd);

Re: max year rolling 12 months

sunny_talwar — Wed, 25 Jan 2017 02:25:19 GMT

I am not sure I follow... can you share a sample?

Re: max year rolling 12 months

everest226 — Wed, 25 Jan 2017 17:25:51 GMT

basically this apps is concurrency analyzer to read server log file,so where i ned to use Date(MonthStart(Date), 'MMM-YYYY') as MonthYear ,, i tried to replace in as year did not work i do need that year field also.

Re: max year rolling 12 months

sunny_talwar — Wed, 25 Jan 2017 22:13:59 GMT

What did not work? I am still not sure I understand

Re: max year rolling 12 months

everest226 — Thu, 26 Jan 2017 19:02:44 GMT

So i was able to get MonthsYear field but somehow it is not working in expression to get 12 months user till last months,, so as of today Jan 2017, chart should have data from Jan 2016 to dec 2016

Count (DISTINCT {<Year={'$(=max(MonthYear))'}, Month-={"$(=month(date#(max({<Year={'$(=max(MonthYear))'}>}Month),'MM')))"}>} [ user])

Re: max year rolling 12 months

sunny_talwar — Thu, 26 Jan 2017 19:04:46 GMT

That is not the expression I gave. I gave you this.... did you try this?

Count (DISTINCT {<Year, Month, MonthYear = {"$(='<' & Date(Max(MonthYear),'MMM-YYYY'))"}>} [user])

Re: max year rolling 12 months

everest226 — Thu, 26 Jan 2017 19:11:24 GMT

yes i did , Expression is ok but the chart says no data to display.

Re: max year rolling 12 months

everest226 — Thu, 26 Jan 2017 19:15:31 GMT

do i need to use monthsyear field in dimension also

Re: max year rolling 12 months

sunny_talwar — Thu, 26 Jan 2017 19:21:32 GMT

Try this:

Count (DISTINCT {<Year, Month, MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -12),'YYYY-MMM') & '<' & Date(Max(MonthYear),'YYYY-MMM'))"}>} [user])

Re: max year rolling 12 months

everest226 — Thu, 26 Jan 2017 19:27:32 GMT

Thanks Sunny