Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Naule
Contributor II
Contributor II

Display the 5 weeks ago up to and including last week in a chart

I'm working on a complaint dashboard and further reporting. 

Regardless of what selections are made, I want there to be an overview chart displaying the total number of complaints for 5 weeks ago, upto and including last week (so not the current week). 

I have setup an alternate state for the chart to function in. 

I have also set up 5 variables for the weeks to be displayed:
vWeek1 = 'W'&right(Year(Today()-35),2)&Week(Today()-35)
...
vWeek5= 'W'&right(Year(Today()-28),2)&Week(Today()-28)

This will display the weeknumber as WeekKPI2 in my datamodel, which I hoped I could plot on the axis to express my number of complaints against. 

I have set up my dimension as :
if(match(WeekKPI2,$(vWeek1),$(vWeek2),$(vWeek3),$(vWeek4),$(vWeek5)),WeekKPI2)

Now I expected the 5 weeks to be shown along the X axis of my graph, but instead a '-' is shown, showing the total number of all complaints. 

How to go about the above? I thought this would be the way to go, I'm not sure if I'm just an inch off, or a full mile. 

1 Solution

Accepted Solutions
Naule
Contributor II
Contributor II
Author

Found a solution meanwhile, replying for refererence sake:

I've set document triggers on 'Open' setting 'WeekKPI2' in the alternate state to the 5 created variables:

OnOpen --> Select in Field --> Field: 'WeekKPI2', --> Value: ='('&'$(vWeek1)'&'|'&'$(vWeek2)'&'|'&'$(vWeek3)'&'|'&'$(vWeek4)'&'|'&'$(vWeek5)'&')'

View solution in original post

2 Replies
Naule
Contributor II
Contributor II
Author

Quick clarification on the variables, due to typo:

I have also set up 5 variables for the weeks to be displayed:
vWeek1 = 'W'&right(Year(Today()-35),2)&Week(Today()-35)
vWeek2= 'W'&right(Year(Today()-28),2)&Week(Today()-28)
...
vWeek5= 'W'&right(Year(Today()-7),2)&Week(Today()-7)

Naule
Contributor II
Contributor II
Author

Found a solution meanwhile, replying for refererence sake:

I've set document triggers on 'Open' setting 'WeekKPI2' in the alternate state to the 5 created variables:

OnOpen --> Select in Field --> Field: 'WeekKPI2', --> Value: ='('&'$(vWeek1)'&'|'&'$(vWeek2)'&'|'&'$(vWeek3)'&'|'&'$(vWeek4)'&'|'&'$(vWeek5)'&')'