Help: Broken set analysis

[mcQlikin]

I have a table that looks something like this. My goal is to display, by Department, the quarter-to-date sum of actuals, plus the remaining year's forecast, to arrive at the best-guess forecast for the year. For example, if current reported quarter is 2, I would need to sum Q1 + Q2 actual, plus Q3 + Q4 forecast 

Department	Quarter	Category1	Category2	Data
AA	1	Revenues	Actual	xxx
AA	2	Revenues	Forecast	xxx
AA	1	Expenses	Actual	xxx
AA	2	Expenses	Forecast	Xxx
BB	1	Revenues	Actual	xxx

My best attempt at this has left me with a working "forecast" expression, but dysfunctional "actual" expression. The forecast expression works beautifully, but for the actuals, whenever I click on a department or something on the sheet, it goes to zero. 

VNumber is a variable set to a certain number (1-4) to tell it which quarter we are reporting on. 

NOT working (to sum actuals): 

Sum({1<[Quarter]={"<=$(=num($(VNumber)))"}>*1<[Category1]={'Revenues'}>*1<[Category2]={'Actual'}>} Data) 

 Working (to sum forecast):

Sum({1<[Quarter]={">$(=num($(VNumber)))"}>*1<[Category1]={'Revenues'}>*1<[Category2]={'Forecast'}>} Data)

Has anyone seen a problem like this?

[Jacek]

Maybe this way:

1) Actual Expenses:

sum({<Quarter={"<=$(=$(VNumber))"}, Category1={"Expenses"}, Category2={"Actual"}>} Data)

2) Forecast Expenses:

sum({<Quarter={">$(=$(VNumber))"}, Category1={"Expenses"}, Category2={"Forecast"}>} Data)

3) Actual Revenues:

sum({<Quarter={"<=$(=$(VNumber))"}, Category1={"Revenues"}, Category2={"Actual"}>} Data)

4) Forecast Revenues:

sum({<Quarter={">$(=$(VNumber))"}, Category1={"Revenues"}, Category2={"Forecast"}>} Data)

-----------

Screen with example for Q=2:

[mcQlikin]

Thank you. It still does the same thing - I break the data into two tables, one for the revenues and one for the expenses. When i click on a random department in the revenues table, the expression calculating actuals returns zero in the other table (the expenses table), but stays the same in the revenues table. and vice versa. 

[Jacek]

May I ask you to put example qvf or screen with this issue?

[mcQlikin]

Sure I've taken 3 separate screen shots (separated by the black lines". First one is the Actuals calculation for Revenues and Expenses, they look great! Second one is when I select one of the departments, the Revenues table still looks great, but the Expenses table goes to zero! The third screenshot is the same except I'm selecting something in the Expenses table, and you'll see that the Revenues table go to zero. 

[Jacek]

Did you try my expressions? I just tested for Expenses and not disappear. Question does all data come from one table ?

[mcQlikin]

Yup I used your expressions and still the same thing, and the data is all from just one table. There are linked tables but those are for something else. Perhaps my Qliksense is broken 😞