Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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%')
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.
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]
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%')
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 |
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%')
It worked! Thank you!
Awesome, please close the thread by marking correct answer
Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny
Sunny,
The only option I have is to mark this answer as helpful.
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 .