Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])))
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])))
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
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
Does wonders. Thanks a ton!