Generating a statistic report based on buckets

[#1]

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

[#2]

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

Total Users and Total Unique Users.

Thanks,

Alon

[its_anandrjs]

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

[SunilChauhan]

see the attched file

hope this help you?

[klausskalts]

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

[SunilChauhan]

or u can see ur accurate result using attached file

[its_anandrjs]

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

[its_anandrjs]

Hi,

It is correct to you or not please confirm me.

Regards

Anand

[#9]

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 'MM-DD'..  X N-times	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

[SunilChauhan · Accepted solution]

see the attched file

might help you