topic Re: Re: Set analysis by ignoring one dimension in QlikView

Set analysis by ignoring one dimension

don_qlikview — Wed, 19 Mar 2014 02:02:18 GMT

Hello Everyone,

I am trying to do set analysis and I have two dimensions, dim1 & dim2 and I am doing Sum([Amount]), so I want to ignore dim1 while doing set analysis. I wrote something like Sum({<dim1 = >}[Amount]) but it doesn't work.

Can anyone please help with this?

Thanks,

don

Re: Set analysis by ignoring one dimension

chiru_thota — Wed, 19 Mar 2014 02:29:40 GMT

The syntax you have written is good and it is to ignore dim1 list box selections.

In other words what ever values you select from dim1 list box ,report will ignore those selections and give data for all dim1 values.

you want the same or you want data aggreagtion needs to be done only by dim2 ?

then your expression will be sum(Total<dim2>[Amount])

Re: Set analysis by ignoring one dimension

don_qlikview — Wed, 19 Mar 2014 02:37:14 GMT

Thanks Chiranjeevi. I need aggregation on dim2 and I did it using the expression you wrote, but when I use your expression all the other expressions that I also need becomes blank. Any thoughts on why they are becoming blanks?

Re: Set analysis by ignoring one dimension

chiru_thota — Wed, 19 Mar 2014 04:40:18 GMT

I am not sure why it is happening.Can you give sample application by scrambling data ?

you can also use aggr function

Example:

aggr( sum(Sales), Country )

aggr( nodistinct sum(Sales), Country )

aggr( sum(Sales), Country, Region )

count( aggr( sum(Sales), Country ))

Re: Re: Set analysis by ignoring one dimension

qlikpahadi07 — Wed, 19 Mar 2014 05:02:51 GMT

Hi Don,

attach a Sample data

PFA it's working do you have similar requirement ?