Your ticket table is not linked to your master calendar, so you will get a total for each month.
You could try using
=Count (if(CalendarDate=daystart(T_STMP_PROB_RSLVD), I_TCKT))
to get a count of tickets for each month. But it would be much better to link the tables, like
daystart(T_STMP_PROB_RSLVD) as CalendarDate
(ooxml, embedded labels, table is Sheet1);
Each date or timestamp has a numerical representation, this allows to calculate with dates or timestamps (e.g. calculating the difference of two dates).
For example, today() is 2012-01-02, with a numerical representation of 40910. But now() is a timestamp around 1:26 am of today's date and its numerical representation is 40910.059872685.
The decimal places are specifying the time of day. daystart() ist returning the whole integer, which is equal to 12 am and this value is also used for the date itself.
So if I create a calendar with dates, I get only integer values like 40910 (and the text representation). If I link my timestamp field to my calendar, I won't get a match for any timestamps that are different from 12 am. So I need to convert my timestamps to dates before linking them to my calendar.
And if you are still asking about the daystart function, this is what the Help says:
Returns a value corresponding to a timestamp with the first millisecond of the day contained in timestamp. The default output format will be the TimestampFormat set in the script. Shift is an integer, where the value 0 indicates the day which contains date. Negative values in shift indicate preceding days and positive values indicate succeeding days. If you want to work with days not starting midnight, indicate an offset in fraction of a day in dayoffset, e.g 0.125 to denote 3am.
daystart ( '2006-01-25 16:45' ) returns '2006-01-25 00:00:00' with an underlying numeric value corresponding to '2006-01-25 00:00:00.000'
daystart ( '2006-01-25 16:45', -1 ) returns '2006-01-24 00:00:00' with an underlying numeric value corresponding to '2006-01-24 00:00:00.000'
daystart ('2006-01-25 16:45', 0, 0.5 ) returns '2006-01-25 12:00:00' with an underlying numeric value corresponding to '2006-01-25 12:00:00.000'
Hope this helps,
Hi Swehl can you please help me on this post......
I have a intersting requirement i.e. i have to display the data in Hierarchy view(Tree Structure) like
D E F G
Here i have attached my sample file and xl file also. I have requirement
1)If you select Austrila from list box in Hierarchy structure we dnt havedata for City listboxes for both side then those listboxes shoulddisappear (wheneevr we dnt have the data for any list box that shoulddisapper) and suppose we dnt have data for Region either left or rightside from hierarchy that list box should disappear and the arrow has directly point to State list box.
2)Based on a Column am dividing the whole structure into 2 parts(left,Right) but in my report am not able to get that correctly am gettingsame data for both sides for this am using a condition like this""=If((Type ='Region') & (LOC ='Left'),Trim(Desc))"".
So please help me to get out of this