topic Set analysis in QlikView

Set analysis

Anonymous — Wed, 07 Nov 2012 16:42:26 GMT

Hello,

I have this expression which calculate recoveries SUM(PAY_AMOUNT) - Sum ({<PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT), now i would to know the actually recoveries made in the previous years, so would i be wrong to write these expressions assuming the current Year is 2012;

SUM({$<Year = {2011}>}PAY_AMOUNT) - Sum({$<Year={2011}>}{<PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT) and

SUM({$<Year = {2010}PAY_AMOUNT) - Sum({$<Year={2010}>}{<PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT) for the previous years respectively.

Thanks

Re: Set analysis

CELAMBARASAN — Wed, 07 Nov 2012 17:09:21 GMT

Try this

SUM({$<Year = {$(=Max(Year)-1)}>}PAY_AMOUNT) - Sum({$<Year={$(=Max(Year)-1)},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT) and

SUM({$<Year = {$(=Max(Year)-2)}PAY_AMOUNT) - Sum({$<Year={$(=Max(Year)-2)},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

Re: Set analysis

Anonymous — Thu, 08 Nov 2012 00:36:13 GMT

Is there variable(s) i have to create in your above expressions?

Re: Set analysis

jagan — Thu, 08 Nov 2012 07:19:11 GMT

Hi,

No need to create any variables for this, the below expressions are dynamic it calculates the years and uses it in the expression.

Suppose if you select year 2012 then the epxression

=Max(Year)-1 get max selected year 2012 and subtracts 1 from it and returns 2011and that value is used in expression.

=SUM({$<Year = {$(=Max(Year)-1)}>}PAY_AMOUNT) - Sum({$<Year={$(=Max(Year)-1)},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

and

SUM({$<Year = {$(=Max(Year)-2)}PAY_AMOUNT) - Sum({$<Year={$(=Max(Year)-2)},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

Hope this helps you.

Regards,

Jagan.

Re: Set analysis

Anonymous — Thu, 08 Nov 2012 08:26:15 GMT

Well the expressions looks okay, but the end result seems the same.Please find attached.Thanks

Re: Set analysis

jagan — Thu, 08 Nov 2012 08:56:27 GMT

Hi,

I think = is missed in above expressions. Try below expressions

=SUM({$<Year = {'=$(=Max(Year)-1)'}>}PAY_AMOUNT) - Sum({$<Year={'=$(=Max(Year)-1)'},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

and

SUM({$<Year = {'=$(=Max(Year)-2)'}>}PAY_AMOUNT) - Sum({$<Year={'=$(=Max(Year)-2)'},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

Regards,

Jagan.

Re: Set analysis

Anonymous — Thu, 08 Nov 2012 09:07:39 GMT

Hi Jagan,

Thanks for your quick response allow me to ask is the Year suppose to the same year field in the application?

Re: Set analysis

jagan — Thu, 08 Nov 2012 10:17:50 GMT

Hi,

Year is the dimension in the file, Please find attached file for solution.

Regards,

jagan.

Re: Set analysis

jagan — Wed, 14 Nov 2012 10:36:06 GMT

Hi,

Can you attach the sample file that you are working?

Regards,

Jagan.