topic Re: Set Analysis Help Required for expression in QlikView

Set Analysis Help Required for expression

vikasmahajan — Thu, 07 Mar 2013 05:27:45 GMT

Dear all

I have following expression for like wise comparision between 28/02/2013 vs 29/02/2012 (Current Year and Previous Year)

but when I compare 28 feb month with last year 28 likewise analysis is wrong , I want to show sales of current year feb with last year

feb as we know last year feb was leap year

Can any one help me in this regards.

My expression is follows:

=(SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},

[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>}

[Stat Sales Amount])

SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},

[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>}

[Stat Return Amount])

)/100000

Thanks

Vikas

Re: Set Analysis Help Required for expression

Thu, 07 Mar 2013 05:48:59 GMT

Hi Vikas,

You can tackle it through load script:

SQL Tips:

SELECT * FROM <Date_table>. WHERE DATE_FORMAT(<Date_table>.Posting Date, '%m') = MONTH(CURDATE())-1 AND DATE_FORMAT(<Date_table>.Posting Date, '%Y') = YEAR(CURDATE())-1

OR in Dimension you can control:

=if( InMonth(Posting_date, now(0), -12 ) , Posting_date)

OR There might be other factors in set expression that are violating. please upload file for more help

Please mark answer Helpful or correct if it work for you.

Re: Set Analysis Help Required for expression

perumal_41 — Thu, 07 Mar 2013 05:58:03 GMT

Hi Vikas,

Try below Expression

(SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},

[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear-1))}>}[Posting Date]))"}>}

[Stat Sales Amount])

SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},

[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear)-1)}>}[Posting Date]))"}>}

[Stat Return Amount])

)/100000

I don't have Application ,so I am tested .this helpful ,i thing.

Regards

Perumal A

Re: Set Analysis Help Required for expression

jagan — Thu, 07 Mar 2013 06:30:39 GMT

Hi Vikas,

Use MonthEnd() in your expression instead of AddMonths()

=Addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))

with this

=MonthEnd(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))

MonthEnd function takes care of the months end date.

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis Help Required for expression

vikasmahajan — Fri, 15 Mar 2013 06:25:35 GMT

Dear Jagan

using monthend function in expression I am not able to compare 15-Mar Vs 15-Mar-12 like wise comparision

since I used monthend in last year column it is taking full month data for lastyear hence there is wrong growth calculated in report.

Is there any way to resolve 28-feb-2013 vs 28-feb & 29-Feb 2012 (Last year) likewise comparision.

Please help

Thanks

Vikas

Re: Set Analysis Help Required for expression

perumal_41 — Fri, 15 Mar 2013 06:48:11 GMT

Hi Vikas,

are u facing any problem this ex[pression

(SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},

[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear-1))}>}[Posting Date]))"}>}

[Stat Sales Amount])

SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},

[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear)-1)}>}[Posting Date]))"}>}

[Stat Return Amount])

)/100000

Regards

Perumal A

Re: Set Analysis Help Required for expression

vikasmahajan — Fri, 15 Mar 2013 06:53:02 GMT

Dear Mr.Perumal A,

I will test and reply you Thanks for help.

Vikas

Re: Set Analysis Help Required for expression

jagan — Fri, 15 Mar 2013 07:04:36 GMT

Hi Vikas,

Calculate the date in variable

vDate = If(Max([Posting Date]) = MakeDate(Max(Year), 2, 28), MonthEnd(Addmonths(Max([Posting Date]), -12)),

Addmonths(Max([Posting Date]), -12))

Now use this variable in your set analysis expression.

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis Help Required for expression

vikasmahajan — Fri, 15 Mar 2013 07:39:46 GMT

Hi,

Jagan Thanks

Insted of Var can use in set analysis direct ?

Re: Set Analysis Help Required for expression

vikasmahajan — Fri, 15 Mar 2013 07:42:14 GMT

Hi,

Jagan Thanks

Insted of Var can use in set analysis direct ?

Re: Set Analysis Help Required for expression

jagan — Fri, 15 Mar 2013 07:51:41 GMT

Hi Vikas,

You can do it in Set Analysis, but it is bit difficult to handle, expression would be very complicate to understand.

Regards,

Jagan.

Re: Set Analysis Help Required for expression

vikasmahajan — Fri, 15 Mar 2013 10:32:28 GMT

Dear Sir

Tested this expression not working I am trying with variables as Mr.Jagan's suggested

Re: Set Analysis Help Required for expression

vikasmahajan — Fri, 15 Mar 2013 12:17:05 GMT

Dear Jagan

I have attach the sample application v1.qvw can u help to arrive the same in this ?

Thanks

Re: Set Analysis Help Required for expression

perumal_41 — Fri, 15 Mar 2013 13:37:55 GMT

Hi Vikas,

I have attached One Application .Check it is Correct or not

Regards

Perumal A

Re: Set Analysis Help Required for expression

vikasmahajan — Mon, 18 Mar 2013 07:32:01 GMT

Dear Sir,

Thanks for giving lot of help me first , I have try your expression but what I want is like wise comparision

means if today is 18 -mar-2013 vs 18-Mar-2012 data should display in your case last year month full

data is showing which is resulting wrong growth

Please see attachment of screen short for the same,.

Thanks again

Vikas

Re: Set Analysis Help Required for expression

vikasmahajan — Mon, 18 Mar 2013 07:32:56 GMT

Dear Sir,

Thanks for giving lot of help me first , I have try your expression but what I want is like wise comparision

means if today is 18 -mar-2013 vs 18-Mar-2012 data should display in your case last year month full

data is showing which is resulting wrong growth

Please see attachment of screen short for the same,.

Thanks again

Vikas

Re: Set Analysis Help Required for expression

perumal_41 — Mon, 18 Mar 2013 08:35:23 GMT

Hi Vikas,

You are selected Year is 2012.So only coming 31/03/2012.you want to crrect means ,select Year 2013 or today date .after check is correct or not

Regards

Perumal A

Re: Set Analysis Help Required for expression

vikasmahajan — Mon, 18 Mar 2013 09:17:29 GMT

Dear Sir

Can u tell me how to use in set analysis

Re: Set Analysis Help Required for expression

vikasmahajan — Mon, 18 Mar 2013 10:29:41 GMT

Dear Sir

Can u tell me how to use in set analysis

Re: Set Analysis Help Required for expression

perumal_41 — Mon, 18 Mar 2013 10:49:29 GMT

Hi vikas ,

Sorry for Delay,

Try like this expression ,

SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},

[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=Date(max({<FiscalYear=,FiscalYear={$(=max(FiscalYear-1))}>}[Posting Date])))}>} [Posting Date]))"}>}

Sale)

I have only written for Sale Value . you try to Return value

Regards

Perumal A