topic Re: Set analysis - Not returning desired result in QlikView

Set analysis - Not returning desired result

adurgani — Wed, 11 Apr 2018 19:53:03 GMT

This is my formula:

Sum({$<Dimension={'Actual'},[Month]={'January','February'}>}[Line Amount]) + Sum({$<Dimension={'April Forecast'},[Month]={'March','April','May','June','July','August','September','October','November','December'}>}[Line Amount])

When I use the month listbox and filter to January I still get the full amount and it doesn't show me just January. How shall I amend the formula to get the desired result?

Re: Set analysis - Not returning desired result

tomasz_tru — Wed, 11 Apr 2018 20:11:29 GMT

And what is the desired result?

Re: Set analysis - Not returning desired result

adurgani — Wed, 11 Apr 2018 20:13:04 GMT

For the data to filter by month. When I select January it still shows me the full year total but I want it to show totals that match selections.

Re: Set analysis - Not returning desired result

vishsaggi — Wed, 11 Apr 2018 20:22:38 GMT

Can you post a snapshot of your data model or some sample data and your expected output?

Re: Set analysis - Not returning desired result

adurgani — Wed, 11 Apr 2018 20:48:13 GMT

Table1:
Dimension	Jan	Feb	Mar	Apr	May
Forecast	20	50	30	40	50

Table 1 is loaded as a cross table with month and Amount

Table 2:
Dimension	Month	Amount
Actuals	Jan	10
Actuals	Feb	20

Table 2 is concatenated into table 1

Desired result is total of all months that don't have an actual (Mar, Apr and May) and Actuals (Jan,Feb). This should be filtered by month as needed. See below for month by month

Jan = 10

Feb = 20

Mar = 30

Apr = 40

May = 50

Re: Set analysis - Not returning desired result

vishsaggi — Wed, 11 Apr 2018 21:15:47 GMT

So what is the Total amount you are looking for? is it 100 or 120?

Try these not sure if this is what you are looking for

CrossTab:
CrossTable (Month, LineAmount,1)
LOAD * INLINE [
Dimension, Jan, Feb, Mar, Apr, May
Forecast, 20, 50, 30, 40, 50
];

LOAD * INLINE [
Dimension, Month, LineAmount
Actuals, Jan, 10
Actuals, Feb, 20
];

= Sum({< Month -= P({<Dimension = {'Actuals'} >} Month) >} LineAmount)

= Sum({< Month = P({<Dimension = {'Actuals'} >} Month) >} LineAmount)

Re: Set analysis - Not returning desired result

adurgani — Thu, 12 Apr 2018 15:29:16 GMT

Thanks so much!

The total in this care is 150. I tried to use the formulas but they aren't working for some reason. What's wrong with this formula

=sum({<Dimension&Month={'ActualsJanuary','ActualsFebruary','ForecastMarch','ForecastApril','ForecastMay'}>}LineAmount)

Re: Set analysis - Not returning desired result

vishsaggi — Thu, 12 Apr 2018 15:45:15 GMT

you cannot use Dimension&Month like that in your set analysis. based on my data set send me the script your are running. 150? How did you get this total and what are the selections you made?

Re: Set analysis - Not returning desired result

adurgani — Thu, 12 Apr 2018 15:50:56 GMT

If the formula worked it would give me a total of 150. see highlighted rows for what's included by month in the 150.

Can I somehow create a concated field the combines month and dimension?

Re: Set analysis - Not returning desired result

vishsaggi — Thu, 12 Apr 2018 15:55:59 GMT

Try like

CrossTab:
CrossTable (Month, LineAmount,1)
LOAD * INLINE [
Dimension, Jan, Feb, Mar, Apr, May
Forecast, 20, 50, 30, 40, 50
];

NoConcatenate
Final:
LOAD *, Dimension&'|'&Month AS DimMonth
Resident CrossTab;

Drop Table CrossTab;

LOAD *,Dimension&'|'&Month AS DimMonth INLINE [
Dimension, Month, LineAmount
Actuals, Jan, 10
Actuals, Feb, 20
];

And use your expr

=sum({<DimMonth={'Actuals|Jan','Actuals|Feb','Forecast|Mar','Forecast|Apr','Forecast|May'}>}LineAmount)

Re: Set analysis - Not returning desired result

adurgani — Thu, 12 Apr 2018 16:02:15 GMT

If we drop the CrossTab table, doesn't the data disappear?

Re: Set analysis - Not returning desired result

vishsaggi — Thu, 12 Apr 2018 16:04:39 GMT

No I am loading that cross table data into Final table which you can see below the NoConcatenate keyword. So Final table holds the data and Actual table will get concatenated to this Final table.

Re: Set analysis - Not returning desired result

swuehl — Thu, 12 Apr 2018 16:08:28 GMT

Try using the intersection operator *= :

Sum({$<Dimension={'Actual'},[Month] *= {'January','February'}>}[Line Amount]) + Sum({$<Dimension={'April Forecast'},[Month] *= {'March','April','May','June','July','August','September','October','November','December'}>}[Line Amount])

or whatever is appropriate for your requirements.

Re: Set analysis - Not returning desired result

adurgani — Thu, 12 Apr 2018 18:43:49 GMT

This is perfect and it worked!!! Thanks much Stefan for the answer and Vishwarath for bringing me along to ask the right questions!! !