How to create a calculate metric that shows daily-specific targets

[I_GK]

Dear all,

I would really appreciate, if you could help me, which function I can use to create a calculate metric that shows daily-specific targets to be able to make a comparison of measured daily passengers versus static daily-specific target numbers.

I have tried a nested if function as per the attachment below. I was thinking to use "Day" instead of "Month of Year" and create 31 segments for all days of the month. I could then use this to create the if statement. Only thing I would need to do is to update the static numbers each month with the new targets. Unfortunately I can only create spesific segments ie Day= 1/1/2023 and not Day contains 1/.

Any ideas?

Best regards Iraklis

[RobertBlakeley]

If I understand correctly, you want to enter some goals as custom numbers in a report. So something like the following but you want daily instead of monthly:

Rather than using the day dimension in the calculated metric, you can use the position of the day in a day ordered list to associate the custom goal to the day or month or week. (Actually, I just posted something about making a Goals Report.) Here is what that that would look like for month, which has 12 segments. For daily, you would have 365 segments for the year, but otherwise the principal is the same:

1. Create the metric for the  monthly goal. We need to have a way to enter a custom goal for each month and associate that goal with the correct month in a single column. To do this we create a calculated metric that uses several built-in functions.
1. Click the "+" sign next to the metric component to open the metric creator form.
2. In the components bar, click on the "Functions" label, then search for the "If" function. 
   
3. Drag the If function onto the “drop metrics” box. 
4. Next search for the "Equal" function and drag it into the Logical_test box within the If function.
5. Then search for the Cumulative function and drag it into the metric_x box within the Equal section. (The Cumulative function sets a row number to identify each month in the series.)
6. In the Cumulative function set the number box to 0. This is the default so likely you will not actually need to set it. (This starts the counting at 0.)
7. Click "Add" on the "metric" box and select "Static Number".  Enter "1" as the number. (This is the amount the Cumulative function will add for each row and will correspond to the monthly rows.)
   
8. Then, in the Equal section, set "metric_Y" to 1 using a Static number. (metric_Y identifies a given month, (the first month in this case.)
9. In the If function section, set the "value_if_true" to the first month's goal using a Static number.
10. Set the "value_if_false" to 0 using a Static number. (The false box is at the bottom).
11. The metric builder should now look like the following:
    So far, you have used the Cumulative function to number each month in the trended series. You used the Equal function to ask if the month number is 1 (metric_y). If so (the If logic function) set the value_if_true, which is the monthly goal. If not it sets no value. In other words, if the value is Jan (month 1) then set the value to your goal (value_if_true).
12. Next, repeat this all for the next month. Follow the steps above for each month, but set the metric_Y value to 2 (meaning the second month), and the value-if-true to the second month's goal. 
13. Change the metric operator between the sections for the first and second months from divide to "+"
    
14. Repeat this for all the remaining months in the series. There will be 12 sections (one for each month), each section should be separated with an add sign: "+". For a daily report, you would repeat this for each day.
15. Click Save.

For a monthly report, you would use the month dimension in your left report column. For a daily report you would use the day dimension. Your calendar date for the report would be set to the date range you are after.

[Jennifer_Dungan]

Do you need a different target for each day? Or would your daily target be as simple as "X" per day?

If you really need it to be different per Day, did you try "Day of Month" instead of "Day"?

[I_GK]

That makes sense! Thanks Jennifer

[I_GK]

I can confirm that this works, thanks again. Only thing that I cant get to work is the sum on the top of the column. On the attached folder it should say 100+200+300=600. Instead of its saying 100. Is there a way around this? I need the sum in order to create a Week to Date target comparison as well.

Any Ideas are welcome!

[Jennifer_Dungan]

I don't think you can get a total with this type of calculation... Unfortunately in the entire month, there is a "Day of Week = 1" that exists, therefore it will match the first IF statement in the metric....

In your example, you have 100+200+300=600
Is this like a cumulative increase?

or are these just example numbers?

Basically I am trying to figure out if your Daily Goal is 100 so that each day your "cumulative" goal is the total from subsequent days?

100 per Day:

• Cumulative Goal Day 1 = 100
• Cumulative Goal Day 2 = 200 (100 for Day 1 and 100 for Day 2)
• Cumulative Goal Day 3 = 300 (100 for Day 1, 100 for Day 2, and 100 for Day 3)
• etc....

Or is it really that you expect to hit different goals each day?

[I_GK]

Hi Jennifer, thanks for your help!

Its a different pax target each day and it looks like this:

Day 1: 500

Day 2: 450

Day 3: 470

etc

Ideally I wan to create the following comparison:

Daily achieved pax vs Daily Target

Week to Date Pax vs Week to date Target

To do this I was looking for a way to sum the week days target.

e.g

Daily pax                                   Daily target

1000                                               500

Week to date pax                Week to date Target pax

1500                                    500+450+470=1420

[Jennifer_Dungan]

Ok, let me think about this... 

Of course, there is always Report Builder...  where you have a little more control, but let me try a few things to see if I can get both a defined daily target, and a proper total