Exclude Selections but keep set analysis

[Anonymous]

Hi I have a chart that calculates and shows the most recent FTE.  The problem I have is that if I change the 'Month' or 'Year' selection the values disappear.  Below is the expression:

Sum(

{<

Employed_Staff.Headcount_Date = {'$(=Max(Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'}>}

  Employed_Staff.FTE)

I tried the following too, but this also loses the data when I select a month or year:

Sum(

{1< Employed_Staff.Headcount_Date = {'$(=Max(Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'}>}

Employed_Staff.FTE)

I did think that I could detach the chart, but not sure if it will refresh when the new week's data comes into the report?

Is there a better way to do this?

Regards

Phil

[stigchel]

Try with {1} in the dollar sign expansion

Sum(

{<

Employed_Staff.Headcount_Date = {'$(=Max({1} Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'}>}

  Employed_Staff.FTE)

[PrashantSangle]

Try what piet suggested or nullify the field on which you dont want change calculation like

Sum(

{<

Employed_Staff.Headcount_Date = {'$(=Max(Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'},Year=,Month=>}

  Employed_Staff.FTE)

Regards

[Kushal_Chawda]

Please check for that Year and Month selection any value is associated or not based on expression logic.

[Anonymous]

None of those work.

My Year & Month is from a table which is a linked table based on Staff.Headcount_Date.

Could that have anything to do with it?

Regards

[Anonymous]

It works if the current selection includes the latest week though or if there is no selection made.

[PrashantSangle]

One more guess

Sum(

{<

Employed_Staff.Headcount_Date = {'$(=Max({<Year=,Month=>}Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'},Year=,Month=>}

  Employed_Staff.FTE)

Let us know if it work for you or share your app with sample data.

Regards

[Kushal_Chawda]

If you can share the sample it will be helpful

[sunny_talwar · Accepted solution]

On similar lines, may be this

Sum({1<Employed_Staff.Headcount_Date = {'$(=Max({1}Employed_Staff.Headcount_Date))'}, Employed_Staff.Weekly_Monthly={'Weekly'}>} Employed_Staff.FTE)

[Anonymous]

Perfect.  Thanks