topic Re: Year on Year Comparison KPI Text Box using Set Expressions in Set Analysis? in QlikView

Year on Year Comparison KPI Text Box using Set Expressions in Set Analysis?

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am fairly new to qlikview (been 2 weeks), I have a question regarding year on year comparison KPI text box, where I use set expressions in the set analysis rather than a measure field that already exists for which I always get an error and no result.

I have the following fields:

1. Date_Year which is 2016, 2017, 2018....

2. The set expression I use is calculated as count(distinct [operators])/count (distinct [associates]) which is in terms of percentage ('#.## %').

I need an output in text box such that, it gives the difference for current year in comparison with previous year (eg. if 90% for 2018, 80% for 2017, it should give 10% in green as increase or if negative, red as decrease like below image).

Please help since I have found blogs and discussions, but none that utilize set expressions in set analysis. IF ANY ALTERNATIVES OTHER THAN SET ANALYSIS, please suggest.

Thank you in advance.

Re: Year on Year Comparison KPI Text Box using Set Expressions in Set Analysis?

sunny_talwar — Tue, 08 May 2018 20:58:28 GMT

Should be like this

RangeSum(

(Count({<Date_Year = {"$(=Max(Date_Year))"}>} DISTINCT [operators])/Count({<Date_Year = {"$(=Max(Date_Year))"}>} DISTINCT [associates])),

-(Count({<Date_Year = {"$(=Max(Date_Year)-1)"}>} DISTINCT [operators])/Count({<Date_Year = {"$(=Max(Date_Year)-1)"}>} DISTINCT [associates])))

Re: Year on Year Comparison KPI Text Box using Set Expressions in Set Analysis?

haneeshmarella — Tue, 08 May 2018 21:18:11 GMT

Thank you so much Sunny. @

Can you help me the same for month vs previous month, week vs previous week, and day vs previous day?

Instead of Date_year, these dimensions are Date_Month, Date_Week, Date_Day

For example, Date_Day = Day(Date) where Date is MM/DD/YYYY

Re: Year on Year Comparison KPI Text Box using Set Expressions in Set Analysis?

sunny_talwar — Wed, 09 May 2018 02:28:16 GMT

May be like this

Month

Use MonthYear field which is created like this in the scrip

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

and then this

RangeSum(

(Count({<Date_MonthYear = {"$(=Date(Max(Date_Month), 'MMM-YYYY'))"}>} DISTINCT [operators])/Count({<Date_Year = {"$(=Date(Max(Date_Year), 'MMM-YYYY'))"}>} DISTINCT [associates])),

-(Count({<Date_MonthYear = {"$(=Date(MonthStart(Max(Date_Year),-1), 'MMM-YYYY'))"}>} DISTINCT [operators])/Count({<Date_MonthYear = {"$(=Date(MonthStart(Max(Date_Year),-1), 'MMM-YYYY'))"}>} DISTINCT [associates])))

Similarly, you can do the same using WeekStart() function to create a Date_WeekYear field and I would just use Date for Day comparison

Re: Year on Year Comparison KPI Text Box using Set Expressions in Set Analysis?

haneeshmarella — Wed, 09 May 2018 13:21:23 GMT

Does wonders. Thanks a ton!