topic Re: getting unexpected result using Set analysis in QlikView

getting unexpected result using Set analysis

Sat, 15 Feb 2014 17:40:05 GMT

Hi All,

I am having a source table like this

Item	key	price
A	2/15/2013	34
A	1/16/2013	35

Loaded using the following script

LOAD Item,

key,

month(date(key,'YYYYMMDD')) as Month,

price as Price

FROM

When I tried to make a straight table with Item and Month as dimensions, Sum(Price) and Sum({<Month=>}Price) as expressions I am getting the same value over both the expressions as below.

Item	Month	sum(Price)	sum({<Month=>}Price)

A	Jan	35	35
A	Feb	34	34

I was expecting 69 in the fourth column for both rows.

can someone explain it please.

Re: getting unexpected result using Set analysis

swuehl — Sat, 15 Feb 2014 17:51:51 GMT

Please read:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/09/aggregation-scope

So I think your second expression should look like

=sum(total<Item> Price)

Besides this, I think you don't need to format your key with a special date format, when you then want to apply month() function, a

month(key) as Month,

should be enough (assuming the key field is correctly read in as date, i.e. your standard date format is set appropriatly).

Re: getting unexpected result using Set analysis

Sat, 15 Feb 2014 17:52:33 GMT

Hi,

In general, if you made selection/filter on Month field then, the corresponding selection data will be appeared on screen. But, some times, we don't need to be applied filters on graphs/charts even though if you apply filters. So, In this scenario, we use mention those fields with '=' sign in SET analysis.

In your below example,

Sum({<Month=>} Price) means, irrespective of selection on Month field, the SUM will be calculated.

To get clear understanding, try to use a list box for your Month field, and make a selection on "Jan" and see whether your existed chart has reflected with your current selection or not.

Again, try to disable your 4th expression your chart and do the same selection.

Hope, you are clear now.

Re: getting unexpected result using Set analysis

Sat, 15 Feb 2014 17:57:45 GMT

Sets are used to change the selection of data, but they honor your dimensions.

Try this instead:

sum( Total [Price] ).

Regards,

Ben

Re: getting unexpected result using Set analysis

MK_QSL — Sat, 15 Feb 2014 17:57:52 GMT

Change expression for 4th column as

SUM(TOTAL {<Month = >]price) or

SUM(TOTAL price)