Sum of value prior to selected date range

[#1]

Im trying to create an expression that sums up a value but for everyhting prior to the selected dates.

For instance if I select Q1 of 2013 and get $1000 for the sum of the selection, I need to show what I got before this.

I've easily gotten set analysis to work for year by year comparison, but Im stuck on this. The best I was taking all "{1} - {$}" on the date field, but that then included data that did not pertain to the selected date range, but only because it had values that feel in the all range.

Does anyone have a sample?

Sum({<Field_Date={" HELP ME HERE "}>} Total_Amount)

[#2]

Hi,

Try with this:

Sum ({< Field_Date={"<$(=Max(Field_Date))"} >} TOTAL_AMOUNT)

Regards,

Ricardo

[#3]

I tried using "Max" and would just return the same totaled amount. So I tried "Min" since I wanted earliest date and then get everything before that and I get zeros while I know there are values

[#4]

Well, I need more details about your app. I have an idea. Can you share your app?

If It has a lot of rows, reduce the data.

File -> Reduce Data -> Keep Possible Values

Regards,

Ricardo

[#5]

The data set is tiny, probably 20,000 rows. I cannot share the app due it would violate company policy.

[jagan]

Hi,

Try this

=Sum({<Field_Date={'<=$(=Max(Field_Date)'}>} Total_Amount)

If you have any Date filters like Year, Month, Quarter or week then try this

=Sum({<YearDimensionName=, MonthDimensionName=, QuarterDimensionName=, WeekDimensionName=, Field_Date={'<=$(=Max(Field_Date)'}>} Total_Amount)

The above experssion ignores selections of Month, Year, Quarter and Week dimensions.

Regards,

Jagan.

[#7]

Jagan,

Thank you, this has worked to some degree. I did have date filters and by ignoring the selection I was able to get to sum up the amount by using the less than of the Min value of the date. However by ignoring the selections, I now have added more rows of the data to my Straight Table chart than needs to be.

[jagan]

Hi,

Can you explain what you exactly need when any Date filters are selected.

Regards,

Jagan.