topic Re: Week over Week Gauge in QlikView

Week over Week Gauge

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am creating a gauge for a WoW percent change. In my script, I have created a field called week:

LOAD Date,

date(Date,'MM-YYYY') as 'Month-Year',

Year,

Week(Date,'##') as 'Week',

date#(Date,'MM/DD/YYYY') as MMDDYY_Date,

Count,

Channel,

[Output Type],

[Workload Type]

My expression for the Gauage is:

=Num(Sum(DISTINCT{1<Week={$(=Max([Date]))}>}{1<[Workload Type]={'Referral'}>}Count)/Sum(DISTINCT{1<Week={$(=Max([Date])-1)}>}{1<[Workload Type]={'Referral'}>}Count)-1,'▲ 0.0%;▼ 0.0%')

However, the Chart is coming back with 'No Data to display' and that there is an error in the set modifier ad hoc element list.

I would like my chart the percent increase or decrease from the week prior.

Re: Week over Week Gauge

sunny_talwar — Thu, 09 Jun 2016 15:35:40 GMT

May be try this:

LOAD Date,

date(Date,'MM-YYYY') as 'Month-Year',

Year,

Week(Date) as Week,

date#(Date,'MM/DD/YYYY') as MMDDYY_Date,

Count,

Channel,

[Output Type],

[Workload Type]

=Num(Sum(DISTINCT{1<Week = {$(=Max(Week))}, [Workload Type] = {'Referral'}>} Count)/Sum(DISTINCT{1<Week={$(=Max(Week)-1)}, [Workload Type] = {'Referral'}>} Count) - 1,'▲ 0.0%;▼ 0.0%')

But using weeks can be problematic at the turn of the year. So I would suggest using date of WeekYear field to do this kind of calculation.

Re: Week over Week Gauge

Thu, 09 Jun 2016 16:02:16 GMT

Thank you Sunny - that is a problem I ran into that is why I orginally had the max(date) in the expression. However that probably was not consistant with the rest of the expression.

Do you suggest I create a new field in the LOAD:

LOAD Date,

date(Date,'MM-YYYY') as 'Month-Year',

Year,

Week(Date) as Week,

WeekYear(Date) as 'Week-Year',

date#(Date,'MM/DD/YYYY') as MMDDYY_Date,

Count,

Channel,

[Output Type],

[Workload Type]

Re: Week over Week Gauge

sunny_talwar — Thu, 09 Jun 2016 16:09:51 GMT

Lets try with Dates, Try this:

=Num(

Sum(DISTINCT{1<Date = {"$(='>=' & Date(WeekStart(Max(Week)), 'MM/DD/YYYY') & '<=' & Date(WeekEnd(Max(Week)), 'MM/DD/YYYY'))"}, [Workload Type] = {'Referral'}>} Count)

Sum(DISTINCT{1<Date = {"$(='>=' & Date(WeekStart(Max(Week), -1), 'MM/DD/YYYY') & '<=' & Date(WeekEnd(Max(Week), -1), 'MM/DD/YYYY'))"}, [Workload Type] = {'Referral'}>} Count) - 1,'▲ 0.0%;▼ 0.0%')

Re: Week over Week Gauge

Thu, 09 Jun 2016 16:57:42 GMT

It is coming back as ' No data to display'

Below is a sample of the data I am working on. I assume that the start of a week is on a Monday so my gauge would show the % change from week 41 (which would only account for the referrals on October 5th) and week 40 (Septebmer 28th-October 4th).

so Week 40: 4391 Referrals

Week 41: 872 Referrals

Date	Year	Count	Channel	Output Type	Workload Type

9/28/2015	2015	261	Counties	None	Referral
9/28/2015	2015	167	Self Service	None	Referral
9/28/2015	2015	427	Worker Portal	None	Referral
9/29/2015	2015	251	Counties	None	Referral
9/29/2015	2015	207	Self Service	None	Referral
9/29/2015	2015	370	Worker Portal	None	Referral
9/30/2015	2015	290	Counties	None	Referral
9/30/2015	2015	197	Self Service	None	Referral
9/30/2015	2015	437	Worker Portal	None	Referral
10/1/2015	2015	236	Counties	None	Referral
10/1/2015	2015	7	Self Service	None	Referral
10/1/2015	2015	412	Worker Portal	None	Referral
10/2/2015	2015	223	Counties	None	Referral
10/2/2015	2015	209	Self Service	None	Referral
10/2/2015	2015	393	Worker Portal	None	Referral
10/3/2015	2015	27	Counties	None	Referral
10/3/2015	2015	34	Self Service	None	Referral
10/3/2015	2015	105	Worker Portal	None	Referral
10/4/2015	2015	28	Counties	None	Referral
10/4/2015	2015	20	Self Service	None	Referral
10/4/2015	2015	90	Worker Portal	None	Referral
10/5/2015	2015	277	Counties	None	Referral
10/5/2015	2015	183	Self Service	None	Referral
10/5/2015	2015	412	Worker Portal	None	Referral

Re: Week over Week Gauge

sunny_talwar — Thu, 09 Jun 2016 17:17:47 GMT

Made some mistakes in the expression above. This seems to be working:

=Num((Sum(DISTINCT{1<Date = {"$(='>=' & Date(WeekStart(Max(Date)), 'M/D/YYYY') & '<=' & Date(WeekEnd(Max(Date)), 'M/D/YYYY'))"}, [Workload Type] = {'Referral'}, Week, Year>} Count)

Sum(DISTINCT{1<Date = {"$(='>=' & Date(WeekStart(Max(Date), -1), 'M/D/YYYY') & '<=' & Date(WeekEnd(Max(Date), -1), 'M/D/YYYY'))"}, [Workload Type] = {'Referral'}, Week, Year>} Count)) - 1

,'▲ 0.0%;▼ 0.0%')

Re: Week over Week Gauge

Thu, 09 Jun 2016 18:22:55 GMT

It worked! Thank you!

Re: Week over Week Gauge

sunny_talwar — Thu, 09 Jun 2016 19:01:12 GMT

Awesome, please close the thread by marking correct answer

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Week over Week Gauge

Thu, 09 Jun 2016 20:18:16 GMT

Sunny,

The only option I have is to mark this answer as helpful.

Re: Week over Week Gauge

sunny_talwar — Thu, 09 Jun 2016 20:28:50 GMT

Are you responding from your Qlik Community inbox? If you open the link you might see the option to mark the correct answer. If you still don't see it, then this might be the browser issue .