topic Re: How to use addmonths in set analysis? in QlikView

How to use addmonths in set analysis?

Anonymous — Fri, 19 May 2017 07:03:29 GMT

My expression is

count(DISTINCT {1<[Referred to CPS]={'Y'},[Resolved_RTC Month] ={"$(=addmonths(today(),-1))"},[Resolved_RTC Year]={"$(=year(today()))"},BizvsNonBiz_Impact_Incidents={'Business Impacting Incidents'},DateType ={'Resolved_RTC'},[Resolution Code]-={"*Select Resolution Code*"}>}Id)

But my addmonths in expression is not working correctly. I need to calculate the tickets resolved only for the previous month at any given point in time..can somebody please tell me whats going wrong with my set analysis? When i add the addmonths expression my expression stops working completely.

Re: How to use addmonths in set analysis?

kaushiknsolanki — Fri, 19 May 2017 07:10:59 GMT

Hi,

What I can make out from your set analysis is you are using a Month field "[Resolved_RTC Month]" to get the month and the search parameter you have passed is "{"$(=addmonths(today(),-1))"}", which will return a date and not the month and that is the reason why your set analysis is not working.

Your set analysis should be as below.

count(DISTINCT {1<[Referred to CPS]={'Y'},[Resolved_RTC Month] ={"$(=Month(addmonths(today(),-1)))"},[Resolved_RTC Year]={"$(=year(today()))"},BizvsNonBiz_Impact_Incidents={'Business Impacting Incidents'},DateType ={'Resolved_RTC'},[Resolution Code]-={"*Select Resolution Code*"}>}Id)

Regards,

Kaushik Solanki

Re: How to use addmonths in set analysis?

jonathandienst — Fri, 19 May 2017 07:13:41 GMT

What format is the field [Resolved_RTC Month]?

It looks like you are comparing a month field with a date field (from AddMonths()).

Re: How to use addmonths in set analysis?

Anonymous — Fri, 19 May 2017 07:19:45 GMT

Hi Kaushik

count(DISTINCT {1<[Referred to CPS]={'Y'},[Resolved_RTC Month] ={"$(=Monthname(addmonths(today(),-1)))"},[Resolved_RTC Year]={"$(=year(today()))"},BizvsNonBiz_Impact_Incidents={'Business Impacting Incidents'},DateType ={'Resolved_RTC'},[Resolution Code]-={"*Select Resolution Code*"}>}Id)

Thanks it worked but i used monthname instead of month

Re: How to use addmonths in set analysis?

Anonymous — Fri, 19 May 2017 07:20:26 GMT

Hi Jonathan

I was using an incorrect format of month while comparing and rectified it by the above solution..thanks for the suggestion