Exclude a Filtering from a column sum calculation

[danyferde]

Good Afternoon All,

I have a pivot table that shows (divided by different categories) the budgets allocated annually and the related purchase orders.
My problem is that if I consider an annual range (01/01 - 31/12) the total budget count is perfect, but if I choose for example the 1st quarter, all budgets with related purchase orders that have date higher than 31/03 are not calculated.
How can I declare that all dimensions are fixed (name and budget code) and so is the "Total Budget for 2019" field?

My aim is to have Total budget that change only if I'll change department, location or year....

Here below my qvf file

Thanks

D.F

[Vegar]

You can ignore field selections in expressions by using set analysis.  

Eg: 

SUM({<Quarter, Month, YearMonth, Year, Date>}BudgetAmount)

A good read if youre not familiar with SET analysis: http://livingqlikview.com/livingqlik-roots-the-ultimate-qlikview-set-analysis-reference/

[danyferde]

Thanks Vegar,

it's since 1 week that I was trying to fix this set analysis without any effect.

Your tread has been very helpful, but I didn't resolve my issue.

I'm going to attach some picture to better understand the problem, maybe I'm doing something wrong.

For the moment thanks, hoping that you or somebody other could help me.

Cattura1.png shows the only filter for CentroCosto and total budget year(2019)

Cattura2.png shows additional filter for first quarter of the year.

I'm expecting that the values are the same in both cases.

Thanks again.

[Vegar]

I adjusted your TOTAL BDG for 2019 (€) expression

//Orignial
sum( {$<Budget_for_year ={2019} >} (aggr (sum ( DISTINCT [budget_annuale]),[codedef])))

//Edited
sum( {$< Budget_for_year ={2019} >} (aggr (sum ({<[Data_Budget_Po_notnull.autoCalendar.Quarter], [Data_Budget_Po_notnull.autoCalendar.YearMonth] >} DISTINCT [budget_annuale]),[codedef])))

 the edited expression ignores all selection in Quarter and YearMonth.

[danyferde]

Vegar,

my english too is not so perfect  🙂

I tried but always the same, all the records that are not in the range of Q1 disappeare.

Maybe I made some mistake in data retreiving?

I also tried to exclude all the data field in set analysis, but nothing...