Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

getting unexpected result using Set analysis

Hi All,

I am having a source table like this

Itemkeyprice
A2/15/201334
A1/16/201335

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.

ItemMonthsum(Price)sum({<Month=>}Price)
AJan3535
AFeb3434

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

can someone explain it please.

1 Solution

Accepted Solutions
MVP
MVP

Re: getting unexpected result using Set analysis

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).

4 Replies
MVP
MVP

Re: getting unexpected result using Set analysis

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).

Not applicable

Re: getting unexpected result using Set analysis

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.

Not applicable

Re: getting unexpected result using Set analysis

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

Change expression for 4th column as

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

SUM(TOTAL price)

Community Browser