topic Re: Help in set analysis in QlikView

Help in set analysis

Fri, 01 Aug 2014 10:59:36 GMT

Hi Experts,

As I am trying to analyse the two years average of sales comparison in a two pie chart(PY,CY),

Dimention is CustomerType the values are (Excellent, Moderate, Poor, Very Poor, Discard)

My expression for PY ---> =Avg(Aggr(Sum({$<SalesYear={$(=Max(SalesYear)-1)}>} [Sales],CustomerType,SalesMonth))

My expression for CY ---> =Avg(Aggr(Sum({$<SalesYear={$(=Max(SalesYear))}>} [Sales],CustomerType,SalesMonth))

I have a problem, for example when the CustomerType have value of Moderate and Poor for the month of July 2014 means the impact is on July 2013 that shows only Moderate and Poor customers data in the pie chart even i have other values, so how do i resolve this.

thanks in advance.

Re: Help in set analysis

Fri, 01 Aug 2014 11:04:15 GMT

can you elaborate your query or post any sample app

Re: Help in set analysis

Fri, 01 Aug 2014 12:13:25 GMT

Thanks Nirmal raj,

What i want is as i need to exclude the selection of [CustomerType], for July 2013 I have sum of value is 14500 which is the one is correct but when I select the [SalesYear] = 2014 and [SalesMonth] = Jul for last Year it shows only 2600 which is incorrect as I need to show 14500.

Hope its clear.

Re: Help in set analysis

avinashelite — Fri, 01 Aug 2014 12:31:58 GMT

try like this

=Avg(Aggr(Sum({$<SalesYear={$(=Max({1}SalesYear)-1)}>} [Sales],CustomerType,SalesMonth))

CY ---> =Avg(Aggr(Sum({$<SalesYear={$(=Max({1}SalesYear))}>} [Sales],CustomerType,SalesMonth))

Re: Help in set analysis

Fri, 01 Aug 2014 12:50:41 GMT

Avinash, thanks for your reply

tried with your expression, still getting the same old result, when I tried to view the result in list box of [CustomerType], when I select the SalesYear = 2014 and SalesMonth=Jul, its showing only two values (Moderate and Poor), it has to show all the values and tried the below too still no result,

=Avg(Aggr({<CustomerType=>}Sum({$<CustomerType=,SalesYear={$(=Max(SalesYear)-1)}>} [Sales],CustomerType,SalesMonth))