Count occurrences per number of days between two dates

[#1]

Hi,

I am using =Max([Time Period])-Min([Time Period]) to get the number of days between to date ranges per Visitor.

Visitor 1 --- 01/02/2013
Visitor 1 --- 03/03/2013
Visitor 2 --- 01/03/2013
Visitor 2 --- 03/03/2013
…

Dimension = Visitor, Formula =Max([Time Period])-Min([Time Period])

my result list looks like this:
17
16
16
16
15
15
14
14
14

Now I would like to get count per number (occurrences) days and show this as chart.

17 – 1
16 – 3
15 – 2
14 – 3

LOAD [Time Period],
Visitor
FROM
[visitor.xlsm]
(ooxml, embedded labels, header is 1 lines, table is [IS1]);

Thank you

[swuehl · Accepted solution]

Try a calculated dimension:

=aggr(Max([Time Period])-Min([Time Period]), Visitor)

and as expression

=count(distinct Visitor)

[#3]

in your chart expression , you can use Count(Formula)

[#4]

Hi,

First of all I would recommend that you split the Time Period field in the script. So you have a Min Time Period and a Max Time Period by Visitor. So your script would look something like this.

LOAD

         Visitor,

     Date(Min([Time Period])) AS MinDate,

     Date(Max([Time Period])) AS MaxDate,

FROM
[visitor.xlsm]
(ooxml, embedded labels, header is 1 lines, table is [IS1])

Group By Visitor

;

To achieve what you are looking for.

Right click on the tab > New Sheet Object > Chart > Select Bar Chart > Select Next >Dimensions > Select Add Calculated Dimension > Add the following expression > MaxDate - MinDate > Select Ok > Select Next > Add the following Expression in the Expression window > Count(Visitor) > Select Ok

Hope this helps.

Thanks

Zohaib

[#5]

That's it.

Thanks a lot.