Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Week over Week Gauge

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.

1 Solution

Accepted Solutions
Highlighted

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


Capture.PNG

View solution in original post

9 Replies
Highlighted

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.

Highlighted
Not applicable

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]

Highlighted

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

Highlighted
Not applicable

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

      

DateYearCountChannelOutput TypeWorkload Type
9/28/20152015261CountiesNoneReferral
9/28/20152015167Self ServiceNoneReferral
9/28/20152015427Worker PortalNoneReferral
9/29/20152015251CountiesNoneReferral
9/29/20152015207Self ServiceNoneReferral
9/29/20152015370Worker PortalNoneReferral
9/30/20152015290CountiesNoneReferral
9/30/20152015197Self ServiceNoneReferral
9/30/20152015437Worker PortalNoneReferral
10/1/20152015236CountiesNoneReferral
10/1/201520157Self ServiceNoneReferral
10/1/20152015412Worker PortalNoneReferral
10/2/20152015223CountiesNoneReferral
10/2/20152015209Self ServiceNoneReferral
10/2/20152015393Worker PortalNoneReferral
10/3/2015201527CountiesNoneReferral
10/3/2015201534Self ServiceNoneReferral
10/3/20152015105Worker PortalNoneReferral
10/4/2015201528CountiesNoneReferral
10/4/2015201520Self ServiceNoneReferral
10/4/2015201590Worker PortalNoneReferral
10/5/20152015277CountiesNoneReferral
10/5/20152015183Self ServiceNoneReferral
10/5/20152015412Worker PortalNoneReferral
Highlighted

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


Capture.PNG

View solution in original post

Highlighted
Not applicable

It worked! Thank you!

Highlighted

Awesome, please close the thread by marking correct answer

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Highlighted
Not applicable

Sunny,

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

Highlighted

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 .