topic Re: Generating a statistic report based on buckets in QlikView

Generating a statistic report based on buckets

Thu, 25 Aug 2011 11:53:30 GMT

Hi,

I am rather new so it might be an easy question -

Suppose I have a data source of (Sessions):

Username	Day	MinutesInWebsite
User1	1	10
User1	1	20
User2	1	30
User1	2	15

Username | Day | MinutesInWebsite

User1, 1, 10

User1, 1, 20

User2, 1, 30

User1, 2, 15

I would like to generate a report that would display this:


	Day 1	Day 2	Day N...
AverageTime	20 ((10+20+30)/3)	15
Total Users	3	1

---

For the sake of the example I use Day as an integer, most likely though is that I will use a date parameter instead and would like to know how I can define a report that takes such ranges in mind (eg split into week buckets, or months, etc).

If this is a complex question please direct me to the correct reading material.

Much appreciated,

Alon Cohen

Generating a statistic report based on buckets

Thu, 25 Aug 2011 12:01:22 GMT

It would also be helpful to differentiate in the bottom table between:

Total Users and Total Unique Users.

Thanks,

Alon

Generating a statistic report based on buckets

its_anandrjs — Thu, 25 Aug 2011 12:38:21 GMT

Hi,

As i understand you need to aggregate the values when sum the values in the expression you need to aggregate the values by Day.

Regards

Anand

Re: Generating a statistic report based on buckets

SunilChauhan — Thu, 25 Aug 2011 13:13:14 GMT

see the attched file

hope this help you?

Generating a statistic report based on buckets

klausskalts — Thu, 25 Aug 2011 13:13:34 GMT

Will this work for you:.

Dummy Script:

Load * inline [Username,Day, MinutesInWebsite

User1, 1, 10

User1, 1, 20

User2, 1, 30

User1, 2, 15];

Dimension in Pivot chart: Username

Formulas:

Avg Time: avg(MinutesInWebsite)

No of Usres: count(distinct Username)

Now juts drag the formulas left and down and it should work ...

//Klaus

Re: Generating a statistic report based on buckets

SunilChauhan — Thu, 25 Aug 2011 13:15:36 GMT

or u can see ur accurate result using attached file

Re: Generating a statistic report based on buckets

its_anandrjs — Thu, 25 Aug 2011 13:26:51 GMT

Hi,

See the attached sample for clearity of the Average and Count hoe to calculate them i place two types of charts for comaprison in

1. Straight table

2. Pivot table

Also how to use the aggregation function in charts,lets see it.

See this updated file

Regards

Anand

Generating a statistic report based on buckets

its_anandrjs — Thu, 25 Aug 2011 13:50:04 GMT

Hi,

It is correct to you or not please confirm me.

Regards

Anand

Re: Generating a statistic report based on buckets

Fri, 26 Aug 2011 13:15:21 GMT

Hi,

Thank you all for the reply - sorry for late return on my part.

Perhaps I wasn't clear (though your answer was also helpful regardless).

I mean if I store the 'day' as a date parameter and later on I want to be able to group my users by different bucket ranges.

Eg one report on a daily basis, the other on a weekly, then monthly, etc - using the same Date param.

For example:

User1, '01-20-2000', 10

User1, '01-20-2000', 20

User2, '01-20-2000', 30

User2, '01-22-2000', 30

User1, '01-28-2000', 15];

Daily:

Header 1	Day '01-20'	Day '01-22'	Day '01-28'
Average	20	30	15
Total	3	1	1
UniqueTotal	2	1	1

Weekly

Header 1	Week 1 - '01-20' - '01-26'	Week 2- '01-27'- ' '01-13'

Average	90/4 =22.5	15
Total	4	1
UniqueTotal	2	1

Thanks,

Alon Cohen

Re: Generating a statistic report based on buckets

SunilChauhan — Fri, 26 Aug 2011 13:41:43 GMT

see the attched file

might help you

Re: Generating a statistic report based on buckets

Fri, 26 Aug 2011 14:40:33 GMT

Hi, thanks that does look like the right direction for me though still 'hard coded' dates.

I was hoping to be able to set a 'dimensions' parameter - eg: 1, 7, 30, 365 and let the report do the rest.

It seems I need to read about: cycle groups, Add Calculated Dimension -

If I find a generic way to handle this (as opposed to hard-coded dates in the expression) I will post it.

Thanks for the help,

Alon

Re: Generating a statistic report based on buckets

SunilChauhan — Fri, 26 Aug 2011 15:12:30 GMT

will this solve ur problem or still u require any help

Re: Generating a statistic report based on buckets

Fri, 26 Aug 2011 16:02:43 GMT

The only thing missing is out to not have to hard-code the date values into the report,

Once I figure that out (or if anyone knows) that would solve my problem entirely.

Thanks for the help,

Alon

Re: Generating a statistic report based on buckets

Sun, 28 Aug 2011 11:52:27 GMT

Hi everyone,

The way to handle this problem in a generic way (eg same report for day, week, yearly) is to add this:

Pivot table properties -> Dimensions -> Add Calculated Dimension ->

Days- =DayName(Date)

OR Week- =WeekName(Date)

OR Year - =YearName(Date)

This esentially takes your specific row runs it against this filtering function and relates into a proper 'bucket' of day/week/year etc.

Hope it helps,

Alon