Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try a calculated dimension:
=aggr(Max([Time Period])-Min([Time Period]), Visitor)
and as expression
=count(distinct Visitor)
Try a calculated dimension:
=aggr(Max([Time Period])-Min([Time Period]), Visitor)
and as expression
=count(distinct Visitor)
in your chart expression , you can use Count(Formula)
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
That's it.
Thanks a lot.