How to visualize goals from a dataset into a table without aggregating

[Kashgar]

Hi Team,

I have a dataset that identifies training completions for various products. Each row is considered a completion. I have a goal table I joined on this so that each completion will show the goal for that month. I need to create a table that aggregates the total completions, then the unique for that month (not aggregated). I need to do the same for year. If it helps I have included a visual of an example with some details. I could create another dataset, but I would like to create as few datasets as possible and just include in what I call the “Transcript Dataset”. I use this dataset for various data points and visuals.

I might be overthinking this, but any help is appreciated.

[thomask]

Hi,

if you move the Month Goal to the dimension fields, it wont be aggregated. The completions will be just a count of the IDs and % of Goal would be: completions/AVG(month_goal).

For one month and one training type, the goal should be identical in every row, so the AVG should give you just the monthly goal again.

[Kashgar]

Thank you for responding @thomask

This is helpful! Any tips on how I achieve this for a Year to Date goal aggregation?

I tried this:

ifelse(datediff({training_date},${AsOfDate},“YYYY”) = 0, count(employee_id), 0)

This works for completions, but how do I do this for goal? Any Tips?

[Thomas]

Hi Kashgar,
If the year-to-date (YTD) calculation uses a parameter like ${AsOfDate}, I’d recommend to add a filter for future completions as well (as a past date could be selected for the ${AsOfDate}). This would then look like this and you should select Sum as aggregate function in the visual:

ifelse(datediff({training_date},${AsOfDate},"YYYY") = 0 AND datediff({training_date},${AsOfDate},"DD") >= 0, 1, 0)

For the YTD calculation for the goal, the logic depends on whether you want to do that on a monthly or daily level. For both, the selected aggregate function in the visual should be Average.
To get the YTD goal on a monthly level, you could use the following calculation, which multiplies the number of month until the selected date with the monthly goal:

extract("MM",${AsOfDate})*{monthly_completions_goal}

To get the YTD goal on a daily level, you could use the following calculation:

(extract("MM",${AsOfDate})-1)*{monthly_completions_goal}+
(dateDiff(truncDate("MM",${AsOfDate}),${AsOfDate},"DD")+1)*{monthly_completions_goal}/dateDiff(truncDate("MM",${AsOfDate}),addDateTime(1, 'MM', truncDate("MM",${AsOfDate})),"DD")

This makes use of the monthly calculation up until the previous month and then prorates the monthly goal based on the number of days that already passed in the current/selected month. It also factors in the number of days the current/selected month has (as it might be 28, 29, 30, or 31).

Did this answer your question? If so, please help the community out by marking the best answer on your questions as "Solution! If you found a different solution in the meanwhile, we would be happy if you could share it with the community as well.