topic Re: Need Help in Set Analysis in QlikView

Need Help in Set Analysis

Wed, 02 Jan 2013 13:06:20 GMT

Hello Friends,

As I need your help in set analysis, as in the blow data I would like to show the last week Sales in straight table.

The Date is organized like the follow.

Year Week Sales

2010 52 100

2011 01 200

2011 02 500

.........

.......

2011 52 300

2012 01 400

.....................

.............................

2012 52 209

2013 01 225

Regards

Ammu

Re: Need Help in Set Analysis

MayilVahanan — Wed, 02 Jan 2013 13:40:54 GMT

Try likethis

=sum({<Week = {'$(vmax)'}>}Sales)

where vmax = Max(week)

Re: Need Help in Set Analysis

Wed, 02 Jan 2013 13:57:35 GMT

Hello Mayil Vahanan.

Thanks for your reply, Okay its working, now the max week is 52, minimum is 1. so for next week the year is 2013 and the week is 1, how will the expression will work.

Could you please clarify...

Regards

Ammu

Re: Need Help in Set Analysis

SunilChauhan — Wed, 02 Jan 2013 14:06:14 GMT

use below code

sum({<Year={$(=max(Year))},Week={$(=max({<Year={$(=Max(Year))}>}Week))}>}Sales)

it will calculate max week on the basis of Max Year

hope this helps

Re: Need Help in Set Analysis

Thu, 03 Jan 2013 13:34:29 GMT

Hello Luminary Sahatwar,

Thanks for your reply, still I am unable to fix the issue, I have attached the qv.

Could you please check and update me.

Regards

Ammu

Re: Need Help in Set Analysis

CELAMBARASAN — Thu, 03 Jan 2013 14:00:59 GMT

I have modified your script as

lOAD *, Year&Num(Week,'00') AS YearWeek Inline

[Year, Week, Sales

2012, 32, 434

2012, 33, 457

2012, 34, 480

2012, 35, 277

2012, 36, 251

2012, 37, 242

2012, 38, 243

2012, 39, 250

2012, 40, 228

2012, 41, 247

2012, 42, 239

2012, 43, 277

2012, 44, 298

2012, 45, 314

2012, 46, 349

2012, 47, 349

2012, 48, 358

2012, 49, 336

2012, 50, 321

2012, 51, 300

2012, 52, 306

2013, 1, 291

];

Then use expression as

If you have only one entry per week then use this

=FirstSortedValue({<YearWeek-={$(=Max(YearWeek))}>} Sales, -YearWeek)

If you have more than one entry per week the use this

=FirstSortedValue({<YearWeek-={$(=Max(YearWeek))}>}Aggr(Sum(Sales),YearWeek), -YearWeek)

Hope it helps

Celambarasan

Re: Need Help in Set Analysis

Fri, 04 Jan 2013 11:13:38 GMT

Hello Celambarsan,

Thanks for your reply, your expression is working in test and loading through in line.

But when I try to retrived the data from DB, it still getting error like -. The previous expression which is working fine upto 52 Week, only the begining of 1st week I am getting such error, If I am in 2 Week, the old expression which is working fine.

=Sum({<WEEK={"$(=max({<YEAR={"$(=max(YEAR))"}>}WEEK-1))"}>}Sales)

can you tell me why its not working..

Regards

Ammu