sum expression without effect of filters

[#1]

Folks,

I have daily data in my tables. I am trying to build an expression to get the sum OUTBOUND DAILY of the last 30 days:

sum({<Date_Daily = {">=$(=(Today()-30)) <$(=Today())"}>} [OUTBOUND DAILY])

The formular works fine as long as i don't filter to specific dates for other diagrams. As soon as i filter the dates, i just got the volume of the 30 days within the selected days. I would like to get always back the full 30 days. But other selections (on other fields of the table) shall apply to the result.

Thanks for any hint,

Regards Mathias

[swuehl]

You probably need to clear all calendar fields the user may select in, like Date, Month, Year, so try something like

sum({<Date_Daily = {">=$(=(Today()-30)) <$(=Today())"}, Date=, Month=, Year= >} [OUTBOUND DAILY])

(add other calendar or change to your field names if needed)

Hope this helps,

Stefan

[rajeshvaswani77]

Hi Mathias,

Could you try this?

sum({1<Date_Daily = {">=$(=(Today()-30)) <$(=Today())"}>} [OUTBOUND DAILY])

I have added 1 in the fornt. Now it must sonsider all teh data without taking in consideration the current selections.

thanks,

Rajesh Vaswani

[#4]

Thanks Guys, both things didn't work.

On swuehls answer I got the amount of all days (not recognizing the 30 day filter anymore),

On Rajeshs answer it doesn't take into account other selections i made,

Thanks

[swuehl · Accepted solution]

You should clear all fields except the ones you use the set modifier on, so if you modify Date_Daily, don't clear it!

If this doesn't work, could you describe your data model a bit closer (especially how you interact with date and time fields)?

[#6]

Hi,

i don't know how, but it works

Just to clarify one detail:

I have cleared all date fields, with the exception of th Date_Daily (which is set in the expression itself). Whatever I choose, the correct value is responded.

Question now: I clear my Date_Monthly and my Date_Weekly field. Logical is that this will be excluded in the expression. But if I filter on some values out of my Date_Daily the correct data still is displayed (which is good). Is it because the filter set in the expression is overwriting the selection in the fields, if it is the same field?

Do you know what I am trying to ask?

Thanks,

Cheers Mathias

[swuehl]

Yes, I think this is correct: set modifier in a set expression override selections in these field.

You can keep your selections even when using set modifier by using set operators (i.e. intersection operator * or union operator +, depending on your needs, check the Help for more detail).

Question now: I clear my Date_Monthly and my Date_Weekly field. Logical is that this will be excluded in the expression.

I'm not sure if I understand. Date_Monthly and Date_Weekly will not be excluded, the set expression will just ignore any selections you've done in these fields, so only Date_Daily is determining the set of records to aggregate over.

[#8]

Thanks for the clarification. You understood my completely right. I should have written: ignored instead of excluded.

Real good explanantion, great help swuehl!!!