topic Re: Problem using the set analysis in QlikView

Problem using the set analysis

Wed, 21 Sep 2011 02:43:13 GMT

Hi,

I have a bar chart showing the total sales for each year (X-dimension = Years, Y-dimension = Total amount of sales). In the raw data set/database, i have the total amount of sales for each quarter. Fyi, one year is divided into 4 quarters. If i would like to show the total amount of sales for the last two quarters only of each year, how should i accomplish it using the set analysis? Why the below set expression does not work?

sum( {$<QUARTER={$(=Max(QUARTER)), $(=Max(QUARTER))-1}>} AMOUNT)

Thanks much in advance for your help!

Problem using the set analysis

Wed, 21 Sep 2011 03:38:35 GMT

Hi,

You can right the expression like below:-

Sum({$<QUARTER={"$(=Max(QUARTER))"}+{"$(=Max(QUARTER)-1)"}>}AMOUNT)

Hope this helps.

Cheers,

Problem using the set analysis

Wed, 21 Sep 2011 03:48:21 GMT

Thanks DJ ... Have tried but it does not work. Thanks.

Problem using the set analysis

Wed, 21 Sep 2011 03:58:21 GMT

Can you pls share the sample application?

Re: Problem using the set analysis

Wed, 21 Sep 2011 04:16:54 GMT

Hi, kindly please find the attached file. I would like to have the chart to show the total sales amount of the last two quarters (Quarter 3 and Quarter 4) for each Year. So, in my example, Fiscal Year 2011 should show 700 (300 + 400), Fiscal Year 2010 should show 1500 (700 + 800). Thanks.

Re: Problem using the set analysis

gussfish — Wed, 21 Sep 2011 04:27:58 GMT

OK, firstly, your field is called FISCAL_QUARTER, not QUARTER, so that's

what you need in your expression to fix your problem.

But then, why use MAX at all? You could just use

{<FISCAL_QUARTER={3,4}>}

The only reason to use MAX is if you want it to sum the last 2 of the

selected quarters. e.g. if the user has explicitly selected quarters 1,

2 & 3, then it will sum Quarters 2 & 3. If that's what you intend, then

yes, do use MAX. Otherwise, hardwire the values.