topic Re: Help: Broken set analysis in App Development

Help: Broken set analysis

mcQlikin — Wed, 22 Dec 2021 18:35:42 GMT

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?

Re: Help: Broken set analysis

Jacek — Mon, 21 Jan 2019 22:05:20 GMT

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:

Re: Help: Broken set analysis

mcQlikin — Mon, 21 Jan 2019 22:25:45 GMT

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.

Re: Help: Broken set analysis

Jacek — Mon, 21 Jan 2019 23:01:02 GMT

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

Re: Help: Broken set analysis

mcQlikin — Mon, 21 Jan 2019 23:34:00 GMT

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.

Re: Help: Broken set analysis

Jacek — Tue, 22 Jan 2019 08:15:27 GMT

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

Re: Help: Broken set analysis

mcQlikin — Wed, 23 Jan 2019 16:29:01 GMT

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 😞