topic aggr function and weekly dimension in QlikView

aggr function and weekly dimension

Thu, 09 Jun 2011 04:16:39 GMT

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!

aggr function and weekly dimension

Anonymous — Thu, 09 Jun 2011 04:24:23 GMT

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.

aggr function and weekly dimension

Thu, 09 Jun 2011 04:31:52 GMT

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?

aggr function and weekly dimension

Anonymous — Thu, 09 Jun 2011 04:34:22 GMT

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.

aggr function and weekly dimension

Thu, 09 Jun 2011 05:54:29 GMT

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

aggr function and weekly dimension

Anonymous — Thu, 09 Jun 2011 06:08:28 GMT

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?

aggr function and weekly dimension

Thu, 09 Jun 2011 06:31:38 GMT

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...

Re: aggr function and weekly dimension

Thu, 09 Jun 2011 22:29:02 GMT

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.