How to count days
Hi guys,
I'm struggeling a bit, trying to figure out how I can count the days in a contact history table (to be used in sales effort stats)
I have a "contact-History" table
in which there is a "date" field
there can of course be many records with the same date (since many companies can be contacted every day)
what I need to know is: how many days are there in a certain period of time ( need to calculate how many attempts, conversations, presentations etc. per day)
example: one month I could have 300 records (= 300 date fields) but the ammount of days is only 15 - (and the ammount of days is what I need to know)
How do I white that in a fx field in order to get the number of days?
Thanks, John :)
11 replies
-
You will need to use the days functions.
days(datefield1, datefield2)
-
Thanks for your effort Fred :) - but once again I believe I fail making myself clear - so I hope an image will do a better job:
Hope this makes better sense
J-)
-
I would say that you need to gather the dates in an array, and then compute cnt(unique( that array )).
-
Thanks Alain,
the way I understand what you say is that the array will collect all the dates (where type is 1 or 6) and then the computing will discard all "dublets" and count only the "uniques" - so far so good.
My problem though is that I haven't got the slightest idea about how to write the array or where (I guess it would be in the fx formula field ?)
So, how do I gather the dates in an array?
kr
J-)
-
To count the number of records where type is this or that, I bet that you are implicitly building an array, and then counting the number of elements. What is the formula you use to solve problems #1 and #2?
-
That helps a lot.
So let us start small. If we want to find all records where field Type = 1 (or "Calls") in the History table then figure out how many days elapsed between the earliest date and the latest date we could do something like:
let xHist := ((select History)[Type = 1] order by Date); <--grabs all records then orders the results by ascending date
let FDate := first(xHist.Date); <--since the records are sorted by ascending date grabbing the first record will give us the earliest date
let LDate := last(xHist.Date); <--then this grabs the last record with the latest date
days(FDate,LDate)<--this finds the difference between the two days from above
Let us know if this is what you were looking for.
-
Thanks guys,
Alain - the formula I use goes like this - cnt((select History)[Type = 6]) <-- and Type = 1 for the other fx
and to get both values in one field I use this: cnt((select History)[Type = 1]) + cnt((select History)[Type = 6]) <-- Returns the total number of records.. in this case 7.
These 7 records are spread out over 3 different days where phonecalls have been an activity. I want to use the number for statistics in order to get an average per activity day
- example: over the last let's say 2 months (60 days), we have had 14 days of customer calling. We have picked up the phone 120 times (type 1 + type 6) which gives us an average per "customer calling" activity day of 8,57 (120 divided by 14)
Fred, I tried your snippet and it returns "1" - which I asume is the number of days between the first and the last date. But that is not really what I'm after - pls. see the above.
Again guys, thanks for your efforts - I'm learning a lot from this :)
-
Hi John -
Going off the data in your picture, 1 day would be the appropriate result for the days command as there are 3 records with Type = Call and the earliest date is 17/01/2022 and the latest date is 18/01/2022. So the days command looks to find the number of days between two dates and 1 is the correct answer.
But I think you want the results to be two days. So you can take the formula and just add 1 to the results and it will tell you the number of days that you want.
Then you can use that result to multiply by the number of days and you will have the answer you need.
-
This formula:
(select History)[Type = 6])builds an array containing handles to the records where "Type = 6". You are then counting the number of elements in that array. To build an array containing handles to records where "Type = 1" or "Type = 6", the formula would be:
(select History)[Type = 6 or Type = 1]To build an array containing the dates in the selected records:
(select History)[Type = 1 or Type = 6].DateAnd, finally, to count the unique dates:
cnt(unique((select History)[Type = 1 or Type = 6].Date)) -
Alain, spot on - fan-f...king-tastic !!
So just to translate into human language
: I'm asking the formula to count the unique values in the Date field inside the History table but only where the Type field is 1 or 6.Only 2 questions left then:
1. are the square brackets [ ] always equal to a "where"?
and 2. does "unique" apply to all types of values, or does it only work with date fields?
-
In a "select" statement, the square brackets may always be used instead of a "where" clause. But there is more: they can also be used to filter related records, or to filter the elements of any array containing handles to a set of records.
The "unique()" function must receive an array as its argument, but the element of the array may be of any type - well, I did not test it with all the possible types, but up'to now it has worked by all those I did throw at it.
Content aside
- 2 yrs agoLast active
- 11Replies
- 381Views