Dashboard Help to Compare This Years Data to Last years Data to same date.
![](https://s3-us-west-2.amazonaws.com/media.forumbee.com/i/da4ba86d-1ff7-4a71-8020-63be11a079b1/h/547.png)
So I am in the process of creating an Insights Dashboard. I am needing help in putting together the right syntax to compare this year to present vs last years data to today, but 2023.
See above for what my Insight Dash looks like.
Here is the code I am using for the first row:
#{Weekly Leads}#;
let Variable1 := count(select Leads where 'Lead Entered Date' >= today() - 6);
#{Last Week Leads}#;
let xLeads := count(select Leads where 'Lead Entered Date' >= today() - 13);
let yLeads := xLeads - count(select Leads where 'Lead Entered Date' >= today() - 6);
#{Lead Difference Percetage}#;
let Variable2 := (Variable1 - yLeads) / yLeads * 100;
#{Weekly Applicants}#;
let Variable3 := count(select Leads
where (Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or
Status = 13 or
Status = 14 or
Status = 15 or
Status = 16 or
Status = 17) and
'Lead Entered Date' >= today() - 6);
#{Last Week Applicants}#;
let x2Leads := count(select Leads
where (Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or
Status = 13 or
Status = 14 or
Status = 15 or
Status = 16 or
Status = 17) and
'Lead Entered Date' >= today() - 13);
let y2Leads := x2Leads - Variable3;
#{Application Difference Percentage}#;
let Variable4 := (Variable3 - y2Leads) / y2Leads * 100;
#{Weekly Qualified Percentage}#;
let Variable5 := count(select Leads
where (Status = 2 or Status = 4 or Status = 5 or Status = 6 or Status = 7 or Status = 8 or
Status = 9 or
Status = 10) and
'Lead Entered Date' >= today() - 6) /
Variable1 *
100;
#{Last Week Qualified Percentage}#;
let x3Leads := count(select Leads
where (Status = 2 or Status = 4 or Status = 5 or Status = 6 or Status = 7 or Status = 8 or
Status = 9 or
Status = 10) and
'Lead Entered Date' >= today() - 13) -
count(select Leads
where (Status = 2 or Status = 4 or Status = 5 or Status = 6 or Status = 7 or Status = 8 or
Status = 9 or
Status = 10) and
'Lead Entered Date' >= today() - 6);
#{Qualified Difference Percentage}#;
let Variable6 := (Variable5 - x3Leads) / x3Leads * 100;
#{Weekly Junk}#;
let Variable7 := count(select Leads
where (Status = 18 or Status = 19 or Status = 20 or Status = 21 or Status = 22 or
Status = 26) and
'Lead Entered Date' >= today() - 6) /
Variable1 *
100;
#{Last Week Junk Percenteage}#;
let x4Leads := (count(select Leads
where (Status = 18 or Status = 19 or Status = 20 or Status = 21 or Status = 22 or
Status = 26) and
'Lead Entered Date' >= today() - 13) -
count(select Leads
where (Status = 18 or Status = 19 or Status = 20 or Status = 21 or Status = 22 or
Status = 26) and
'Lead Entered Date' >= today() - 6)) /
Variable1 *
100;
#{Junk Difference Percentage}#;
let Variable8 := (Variable7 - x4Leads) / x4Leads * 100;
10 replies
-
When dealing with dates, I find it handy to create some formula fields that you can put in another tab in Leads.
I would create fields that do:
at least:
year()
month()
quarter()
you can add:
yearquarter()
weekday()
Now you can very quickly filter by any of the fields above. You can also build charts based on these fields as well.
I noticed that you do a LOT of select statements. May I recommend that you do 1 select statement into a variable at the beginning and then filter the variable as you move down the line.
let getLeads := select Leads #{Weekly Leads}#; let Variable1 := count(getLeads['Lead Entered Date' >= today() - 6]); #{Last Week Leads}#; let xLeads := count(getLeads['Lead Entered Date' >= today() - 13]); let yLeads := xLeads - Variable1; #{Lead Difference Percetage}#; let Variable2 := (Variable1 - yLeads) / yLeads * 100; #{Weekly Applicants}#; let Variable3 := count(Variable1 [Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or Status = 13 or Status = 14 or Status = 15 or Status = 16 or Status = 17]);
You already have some variables that you reuse, i.e. Variable1 and xLeads. Maybe you can rename the variable to dateMinus6 and dateMinus13, so you know you can reuse them later down the line. Like in line 6 and 10. I would get lost if I came back to this 6 months from now.
You also have quite a few filters that do the same thing. Try to put those into a variable higher up then you can just call that variable array and filter it some more if need be.
let getLeads := select Leads #{Weekly Leads}#; let dateMin6 := count(getLeads['Lead Entered Date' >= today() - 6]); #{Last Week Leads}#; let dateMin13 := count(getLeads['Lead Entered Date' >= today() - 13]); let min13SubMin6 := dateMin13 - dateMin6; #{Lead Difference Percetage}#; let leadDiff := (dateM6 - min13SubMin6) / min13SubMin6 * 100; #{Weekly Applicants}#; let statusGroup1 := getLeads [Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or Status = 13 or Status = 14 or Status = 15 or Status = 16 or Status = 17] let statusGroup1Min6 := count(statusGroup1['Lead Entered Date' >= today() - 6]);
-
said:
let Variable3 := count(Variable1
[Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or
Status = 13 or
Status = 14 or
Status = 15 or
Status = 16 or
Status = 17])I am running across a function error here with your recommended code. I put statuses in brackets with no luck, changed to a where statement, no luck, and changed to an and statement, still no luck,
-
Changed it to this for the time being.
let Variable3 := count(getLeads[(Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or Status = 13 or Status = 14 or Status = 15 or Status = 16 or Status = 17) and 'Lead Entered Date' >= today() - 6]);
-
So, what would be the method for me comparing data from 01/01/204 to present against 01/01/2023 to (present day and month), 2023
-
said:
Lets take this year. I want to see the data insights from 01/01/20234 to today() and then show the percentage increase/decrease (as I did with monthly and weekly insights from up above), from similar timeframe as last year, i.e. 01/01/2023 to 03/21/2023.Sorry for not being very clear with my explanation.
Let us go back to how I recommended you start:
let getLeads := select Leads
Since you have a lots of Status choice you are looking for maybe putting those in variables will make things easier as you move along. Something like:
let getLeads := select Leads let statusGroupOne := getLeads[Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or Status = 13 or Status = 14 or Status = 15 or Status = 16 or Status = 17] let statusGroupTwo := getLeads[Status = 2 or Status = 4 or Status = 5 or Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10] let junkGroup := getLeads[Status = 18 or Status = 19 or Status = 20 or Status = 21 or Status = 22 or Status = 26]
Now you can use the yearfield or quarterfield to get the data you want.
let statusGroupOne2023Today := statusGroup1[yearfield >= 2023] let statusGroupOne2024Today := statusGroup1[yearfield = 2024] let statusGroupTwo2023Qtr1 := statusGroup2[yearfield = 2023 and quarterfield = 1] etc...
If you had the yearquarterfield then you can use that.
I thought maybe I could do it the same way I did for the monthly and weekly data insights.
You can. Whatever you think works best.
However, I'm thinking about putting a choice field with the above yearly so in the future, it can be compared to other years.
I would recommend this as well. I'm sure you will be using this in to the far future so you need to ready, plus you don't want to be locked into manually updating the code every year.
-
Hey, I took your suggestions from the first post to clean up the code a bit. Here is what I have. Anything else I can do to optimize it?
let presentDate := (select Leads where 'Lead Entered Date' >= today() - 6); let pastDate := (select Leads where 'Lead Entered Date' <= today() - 7 and 'Lead Entered Date' >= today() - 13); #{Weekly Leads}#; let Variable1 := count(presentDate); #{Last Week Leads}#; let pastLeads := count(pastDate); #{Lead Difference Percetage}#; let Variable2 := (Variable1 - pastLeads) / pastLeads * 100; #{Weekly Applicants}#; let Variable3 := count(presentDate[Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or Status = 13 or Status = 14 or Status = 15 or Status = 16 or Status = 17]); #{Last Week Applicants}#; let pastApplicants := count(pastDate[Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10 or Status = 12 or Status = 13 or Status = 14 or Status = 15 or Status = 16 or Status = 17]); #{Application Difference Percentage}#; let Variable4 := (Variable3 - pastApplicants) / pastApplicants * 100; #{Weekly Qualified Percentage}#; let Variable5 := count(presentDate[Status = 2 or Status = 4 or Status = 5 or Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10]) / Variable1 * 100; #{Last Week Qualified Percentage}#; let x3Leads := count(pastDate[Status = 2 or Status = 4 or Status = 5 or Status = 6 or Status = 7 or Status = 8 or Status = 9 or Status = 10]) / pastLeads * 100; #{Qualified Difference Percentage}#; let Variable6 := (Variable5 - x3Leads) / x3Leads * 100; #{Weekly Junk}#; let Variable7 := count(presentDate[Status = 18 or Status = 19 or Status = 20 or Status = 21 or Status = 22 or Status = 26]) / Variable1 * 100; #{Last Week Junk Percenteage}#; let x4Leads := count(pastDate[Status = 18 or Status = 19 or Status = 20 or Status = 21 or Status = 22 or Status = 26]) / pastLeads * 100; #{Junk Difference Percentage}#; let Variable8 := (Variable7 - x4Leads) / x4Leads * 100;
Content aside
- 5 mths agoLast active
- 10Replies
- 60Views
-
2
Following