Week over Week Gauge

[#1]

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.

[sunny_talwar]

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.

[#3]

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]

[sunny_talwar]

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%')

[#5]

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

[sunny_talwar · Accepted solution]

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%')

[#7]

It worked! Thank you!

[sunny_talwar]

Awesome, please close the thread by marking correct answer

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[#9]

Sunny,

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

[sunny_talwar]

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 .