Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to find the average time for records over the last 20 weeks as well as the last 28days. The problem I'm running into is that the date range specified in my set analysis doesn't take effect when wrapped in the aggr function.
For ex. I have 2 KPIs, one for the last 20 weeks and one for the last 28 days as follows:
=Avg(
Aggr(
{<PrimaryDate={">=$(=PrimaryDate20wStart)<=$(=PrimaryDateEnd)"}>}
Time_Arrive_Destination
,ID))
The PrimaryDateEnd field is selected by the user. The PrimaryDate20wStart is defined in the script as PrimaryDateEnd-141 days. I have created a similar field in the data load for the last 28 days called "PrimaryDate28dStart".
Both of these date fields are working properly when added to a table. They also appear correctly in the preview shown when editing each KPI. The problem is that both KPIs for the last 20weeks and 28days are identical, implying that they are averaging the same data/ using the same date range.
However, when I remove the aggr function, both KPIs show different values. Any ideas?
Hi, try adding set analysis to Avg() outside the aggr:
=Avg({<PrimaryDate={">=$(=PrimaryDate20wStart)<=$(=PrimaryDateEnd)"}>}
Aggr(
{<PrimaryDate={">=$(=PrimaryDate20wStart)<=$(=PrimaryDateEnd)"}>}
Time_Arrive_Destination
,ID))
Hi,
Thanks for your reply. I added the set analysis to the avg() but I am still getting the same error; both KPIs for the last 20 weeks and 28 days are identical.
Hi, can you upload a sample app with demo data to make some test? Also check that there is no other field filtreing data, in example if there are selctions on a YearMonth field, it should be set to be ignored by set analysis:
=Avg({<PrimaryDate={">=$(=PrimaryDate20wStart)<=$(=PrimaryDateEnd)"},YearMonth>}
Aggr(
{<PrimaryDate={">=$(=PrimaryDate20wStart)<=$(=PrimaryDateEnd)"},YearMonth>}
Time_Arrive_Destination
,ID))
Or it could be that Time_Arrive_Destination needs a set analysis too:
Only({<PrimaryDate={">=$(=PrimaryDate20wStart)<=$(=PrimaryDateEnd)"},YearMonth>} Time_Arrive_Destination)