aggr function and weekly dimension

[#1]

hi guys,

1) i have been trying to figure out the correct way to use aggr but i seem to be either getting wrong values or invalid values(causing no data to be displayed) in charts...

i have 3 columns: columnA columnB and lastUpdated...

i'm supposed to sum(columnA) and divide it by sum(columnB) to get a % and they have to be aggregated by days

aggr(sum(columnA)/sum(columnB), lastUpdated)

what is wrong?

2) i have also been trying to figure out how to list the dimension in working weeks with the latest/current week right at the end of the axis...

for e.g., if is currently week 3 of the year, the dimensions would be smth like (start) ... ... 46 48 49 50 51 52 1 2 3 (end)

the chart works fine if i simply use the lastUpdated column, which is a Date type. however, i only want to see the week so i did a week(lastUpdated) but in the end i only got a single 53 in the dimension...

what is wrong?

oh by the way, im using qv10 personal edition...

thanks in advance for any help!

[Anonymous]

Hey Axon,

If you go ahead and attach your .qvw file it would be easier to try and figure out. The expression might fail depending on the expressions making up ColumnA and ColumnB.

[#3]

hi Johannes thanks for the reply,

i'm using personal edition so unless you are using the server edition you won't be able to open my file right? and besides there might be some confidentiality issues with my data, is there a way to share my stuffs w/o leaking confidential stuffs?

oh and some of the values have zeroes, maybe that was y the division may not work for them. is there any workarounds to detect the zeroes?

[Anonymous]

Hey Axon,

For a licensed QlikView user it's not a problem to open documents created with personal edition, so that's fine.

When it comes to confidentiality of the data there's a tab under the Settings > Document Properties called Scrambling, where you can scramble any sensitive fields, like customer names etc.

[#5]

i'm able to scamble the data but seem to be unable to upload the file, the progress bar wasn't moving for a long time.

i'll try to explain what what those 3 columns are

the values of 6 rows are as follow:

columnA columnB lastUpdated

100         500        6/9/2011

250         600        6/9/2011

400         500        6/9/2011

0            100        6/7/2011

25           500       6/7/2011

50           1000     6/7/2011

so i'm supposed to (100+250+400)/(500+600+500) for 6/9/2011 and (0+25+50)/(100+500+1000) for 6/7/2011

so is aggr(sum(columnA)/sum(columnB), lastUpdated) the right way?

could you also help me with the 2nd question regarding the weekly dimension

thanks

[Anonymous]

Is this a straight table?

What are the dimensions?

What is the expression for Column A/B? Sum(FieldX)?

What is the expression for lastUpdated?

Or are they just three fields in the document?

[#7]

the expressions for Column A/B and lastUpdated are just themselves, they were not calculated values but just values extracted from the database...

it is the final % from sum(A)/sum(B) that matters...

the dimension for the daily trends chart is,

=if(lastUpdated>=Today()-30 and lastUpdated<=Today(),Day(lastUpdated))

the dimension for the weekly trends chart is,

=if(lastUpdated>=Today()-365 and lastUpdated<=Today(), Week(lastUpdated))

other than the same problem the daily trends chart has, the weekly chart also does not list the week dimension with the current/latest week at the right end of the axis...

[#8]

Couple of suggestions, in your load script do something like this:

Load

     columnA,

     columnB,

     lastUpdated,

     week(lastUpdated) as updateWeek,

     month(lastUpdated) as updateMonth,

     year(lastUpdated) as updateYear

from sourcetable;

Then in your chart, use set analysis instead of the aggr() function.

Sum({$<updateYear={$(=max(year(updateYear)))}>} columnA)

/

Sum({$<updateYear={$(=max(year(updateYear)))}>} columnB)

Now, add the updateYear, Month, & Week fields to list boxes to allow users to dynamically select their time period. Also you can use  Sum({$<updateYear={$(=max(year(updateYear))-1)}>} columnB) to force the calculation to look at the same data from one year ago.

--edit: you can also use the updateYear etc... fields as dimensions within your charts, or add them to a cyclical group and use that as your dimension to easily switch between them.