correct way to use aggr

[#1]

im not sure how to explicitly describe my situation here so i hope my analogy will be easy to understand. i would like to find out the % of sales i make over the total costs grouped by the work weeks...

is this the correct way to use the aggr function to achieve my objective:

=aggr(sum(sales)/sum(costs), date)

all records are inserted at the same time during the end of the week so records of the same week will have the same 'date' as stated above...

when i do this:

=aggr(sum(sales)/sum(costs), week(date))

instead of the statement above, i get nothing displayed...

my chart's dimension is as follows:

=if(date>=Date(Today()-182) and date<=Date(Today()), week(date))

the dimension is over the last half year or 26 weeks and the 'date' here is the same as the 'date' in the aggr statements above...

thanks in advance for any guidance

[IAMDV]

Hi,

Please can you upload the QV document with some sample data? It will be easy to try while looking at your data.

Thanks - DV

[Anonymous]

Also keep in mind that Aggr() can only be used over proper fields as dimension values, not expressions like week(date). In that case you should calculate the week(date) on the script side and then use the resulting field value as a dimension for the Aggr() function.

[#4]

i have a calculated week(date) on the script side but whenever i use them i get no data displayed, the only time i get data displayed is when i use week(date) in aggr()...

i also have a week column in the table already but the dimension does not list itself from the last X number of weeks to the current week, the numbers are all over the range...

[Anonymous]

Post example doc?

Right now I'm a bit confused

[#6]

this is currently my dimension

=if(UTILIZATION_WEEKLY.START_DATETIME>=Date(Today()-182) and UTILIZATION_WEEKLY.START_DATETIME<=Date(Today()),week(UTILIZATION_WEEKLY.START_DATETIME))

if i use UTILIZATION_WEEKLY.START_DATETIME instead of week(UTILIZATION_WEEKLY.START_DATETIME), i get the date instead of the week and that isn't what i want... records belonging to the same group are inserted at the exact same date/time so all the records have UTILIZATION_WEEKLY.START_DATETIME like 2011-06-13 00:00:00, 2011-06-06 00:00:00, 2011-05-30 00:00:00 and so on in the database...

in addition, if i use UTILIZATION_WEEKLY.WORK_WEEK, the order simply screws up since i want the current/latest week on the right end of the axis followed by the previous X number of weeks as stated in the previous post...

from a previous discussion here http://community.qlik.com/thread/29722, i joined databases from different sites and added a new field in the load script so as to be able to differentiate which site the records belong to...

so in my line graphs i have a bunch of lines, one line to show an overall view without the if condition and the other lines are site-specific so they each have an if condition to check againt the new field that was added in the load script...

this is the expression for the overall view...

=aggr((sum(UTILIZATION_WEEKLY.EQP_UPTIME)/sum(UTILIZATION_WEEKLY.TOTAL_TIME))*(sum(UTILIZATION_WEEKLY.PRODUCTIVE_TIME)/sum(UTILIZATION_WEEKLY.EQP_UPTIME))*(sum(UTILIZATION_WEEKLY.TOTAL_GOOD_UNITS)/sum(UTILIZATION_WEEKLY.TOTAL_UNITS_TESTED)), UTILIZATION_WEEKLY.WORK_WEEK)

this is one of the site-specific view, it is the same as the overall view with the addition of the if condition...

=aggr(if(Site='A', (sum(UTILIZATION_WEEKLY.EQP_UPTIME)/sum(UTILIZATION_WEEKLY.TOTAL_TIME))*(sum(UTILIZATION_WEEKLY.PRODUCTIVE_TIME)/sum(UTILIZATION_WEEKLY.EQP_UPTIME))*(sum(UTILIZATION_WEEKLY.TOTAL_GOOD_UNITS)/sum(UTILIZATION_WEEKLY.TOTAL_UNITS_TESTED))), UTILIZATION_WEEKLY.LAST_UPDATED)

notice that i used WORK_WEEK in the overall view but used LAST_UPDATED in the site-specific view... if i use WORK_WEEK in the site-specific view, the line simply disappears... please advise how i could proceed or if i used any of the functions in the wrong way/spot...