topic Help with Set Analysis in QlikView

Help with Set Analysis

Thu, 18 Sep 2014 02:45:58 GMT

Hi All,

I am hoping that I can get some help figuring out what I am doing wrong. I want to create a set analysis (within a straight table) for one of the columns that will show me the total sales of manufacturer "X" for 2014 I used the formula =Sum({$<MANUFACTURER={'X'},Year={'2014'}>},REVENUE_SALES), however the values that I get are the total sales for manufacturer "X" for all years instead of just 2014.

The one good thing is that the values within the set analysis column does not change when I make other selections, however they are not the values that I want.

I would really appreciate helpful suggestions

Thanks!

Re: Help with Set Analysis

nagaiank — Thu, 18 Sep 2014 02:50:25 GMT

Try the expression

Sum({$<MANUFACTURER={'X'},Year={2014}>} REVENUE_SALES)

Re: Help with Set Analysis

Thu, 18 Sep 2014 02:56:09 GMT

Thanks NagaianK, but I am still having the same issue - i.e. if shows all years instead of only 2014.

Re: Help with Set Analysis

Thu, 18 Sep 2014 03:06:05 GMT

is your REVENUE_SALES in the same table as Year?? maybe you should check the way the data are linked.

You can do a simple test by add Year as a list box and add your dimensions and REVENUE_SALES as a table box. check does filter on year list box reduce the data in the table box the way it should be.

Re: Help with Set Analysis

uacg0009 — Thu, 18 Sep 2014 03:25:12 GMT

Dear Ward,

I think GU said is correct, it is possible if your REVENUE_SALES field isn't link to Year, so the Year={2014} won't work for the expression, you need to check the structure of your tables, do you have any field like data or year in REVENUE_SALES table to link to Year?

Re: Help with Set Analysis

Thu, 18 Sep 2014 07:05:24 GMT

Dear Ward,

Aiolos and GU said is Correct. first you check field year in REVENUE_SALES Table and then check in table box.

Re: Help with Set Analysis

Anonymous — Thu, 18 Sep 2014 07:14:27 GMT

Hi ward james,

use below script . In single code you putted the year so that's why its picking all years value in codes we should write only string value.

=Sum({$<MANUFACTURER={'X'},Year={2014}>},REVENUE_SALES)

Thanks

Re: Help with Set Analysis

Thu, 18 Sep 2014 13:35:47 GMT

Thank you for your help, the tables were not linked that's why I was unable to see the changes.

Re: Help with Set Analysis

Thu, 18 Sep 2014 14:05:45 GMT

I tried using the following expression to create the column that contains only the data for Manufacturer X but I am not getting any results, the column shows zero value. The Developer created the Variable DTLKCapAmort to calculate the REVENUE_SALES and by changing QStart and QEnd I can set the paramaters for the dates, however when I try to include the =Sum({$<MANUFACTURER={'X'}>}$(DTLKCapAmort(QStart(-2),QEnd(0),'REVN'))) - the column return zero values.

Any suggestions?

Re: Help with Set Analysis

Thu, 18 Sep 2014 15:20:04 GMT

Use the date variable to specify the year.