How to Sum, allowing only one field to filter....

[#1]

So I am very new to Qlikview and while I've found it very useful, I am running into an issue.  I have spend data that is able to be filtered by location, date, cost center, etc...  I have created a text object which sums all of the spend, regardless of the selections; Sum(ALL SPEND_AMOUNT).  I have another text object which sums the filtered spend; SUM(SPEND_AMOUNT).  I am now being asked if there is any way to create a text object that sums all of the spend, but allows the date to be used as a filter, and only the date.  In essense, they want the spend total to be filtered in this object, but only by this one field.  As a side, the dates are broken out by YEARNAME, QUARTERNAME, and MONTHNAME; allowing people to select one or more years, months, quarters, etc...

Any advice would be greatly appreciated. 

[christian77]

Hi:

Check help, set analysis.

SUM({$<Field={'DesiredValue'}>} SPEND_AMOUNT)

or may be

SUM({1<Field={'DesiredValue'}>} SPEND_AMOUNT)

to avoid other selections

Ok.

[#3 · Accepted solution]

Hi there,

If you want to stop something from filtering the data you do this in the set expression.  So, for example:

Sum(Spend_Amount)  

sums all filtered data

Sum(  {<location=, cost_center=>} Spend_Amount)

sums all spend amount and is not changed by the location or cost_center drop downs.

Hope that helps.

Gareth

[#4]

Thank you very much, this worked like a charm.  You just saved me at least half a day of looking around and trial and error.