Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

line chart having problems with results over more than 1 year

hi guys,

i have a line chart with a bunch of lines, 1 line that shows the overall view and the other lines being location-specific

for the dimension, i am using work weeks. the work weeks is from a calendar table that specifies which week/month/year this particular day is. this table is connected to the date field, START_DATETIME, of other tables, such as UTILIZATION_WEEKLY. anyway, the expression for the dimension is simply =WORK_WEEK and WORK_WEEK is a column in the calendar table.

this is the expression for the overall view line,

=(sum({$<Site = >} UTILIZATION_WEEKLY.EQP_UPTIME)

/sum({$<Site = >} UTILIZATION_WEEKLY.TOTAL_TIME))*

(sum({$<Site = >} UTILIZATION_WEEKLY.PRODUCTIVE_TIME)

/sum({$<Site = >} UTILIZATION_WEEKLY.EQP_UPTIME))*

(sum({$<Site = >} UTILIZATION_WEEKLY.TOTAL_GOOD_UNITS)

/sum({$<Site = >} UTILIZATION_WEEKLY.TOTAL_UNITS_TESTED))

this is the expression for the location-specific view,

=aggr(

(sum(if(Site='A', UTILIZATION_WEEKLY.EQP_UPTIME))

/sum(if(Site='A', UTILIZATION_WEEKLY.TOTAL_TIME)))*

(sum(if(Site='A', UTILIZATION_WEEKLY.PRODUCTIVE_TIME))

/sum(if(Site='A', UTILIZATION_WEEKLY.EQP_UPTIME)))*

(sum(if(Site='A', UTILIZATION_WEEKLY.TOTAL_GOOD_UNITS))

/sum(if(Site='A', UTILIZATION_WEEKLY.TOTAL_UNITS_TESTED)))

, START_DATETIME)

i have a listbox that shows the current year and past 3 years so it's 2008, 2009, 2010 and 2011.

whenever i select a single year, the graph works fine. however when i select more than a single year, some lines will start disappearing or stop/start randomly along the dimension.

what could be the problem or how can i modify my expressions/dimensions to work better?

thanks in advance

PS: i will upload some screenshots to illustrate my problem when im able to as the place i am at now restricts uploads...

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

Try to replace 'START_DATETIME' by 'WORK_WEEK' as dimension in the 'aggr' function.

View solution in original post

5 Replies
pat_agen
Specialist
Specialist

hi,

is WORK_WEEK a number going from 1 to 52? If so then I think your chart will only be guaranteed to work safely when 1 year is selected.

If you want to see continous weeks spanning year sthan you should add a continuos WORk_WEEK with values something like AAAA_1 thru AAAA_52 where AAAA is the year.

Not applicable
Author

thanks for your reply pat,

yes WORK_WEEK is a number going from 1 to 52, so for 2011 there's a set of 1 to 52 and another set for 2010 and so on...

my calendar table has a YEAR column too, can i work out something from there like what you're suggesting?

anyway here are some of the screenshots to better illustrate my situation,


when a single year is selected:

http://i.imgur.com/Trbrc.jpg

2 examples showing multiple years selection:

http://i.imgur.com/XzYQr.jpg

http://i.imgur.com/6GaGL.jpg

nagaiank
Specialist III
Specialist III

Try to replace 'START_DATETIME' by 'WORK_WEEK' as dimension in the 'aggr' function.

pat_agen
Specialist
Specialist

hi,

I wasn't sure what your desired result was. If you want to see weekly results over several years, so that weeks 50,51 and 52 of 2010 are fllowed by weeks 1, 2 and 3 of 2011. Or if you wanted to see weeks 1, 2 and 3 always in the same column.

From the snaphsots posted it would seem that this second solution is what you want.

One solution to look at is teh use of the keyword NODISTINCT in your aggr() function. By default aggr() is expecting one value to come back to it from your dimension START_DATETIME but as you in fact have several years this will not be the case. NODISTINCT should cover this possibility.

the keyword is to be placed dircetly after the aggr( part of your code before your expressions.

The other possible solution woudl be to add YEAR as another dimension tou youarr aggr() statement. But start with NODISTINCT and see if that works for you.

Not applicable
Author

kris, replacing the aggr's dimension with WORK_WEEK worked!

pat, NODISTINCT produces the same result though...

nevertheless, thank you both for the constant help!