topic Re: Need help in set expression in QlikView

Need help in set expression

Wed, 19 Jun 2013 12:08:30 GMT

Hi Friends,

A table that contains Year, Week, invoice number. As I would like to show the Last week value of Purchase_Order

I tried Maximum of Year and Max of Week -1 but it shows error.

YEAR	WEEK	Sum(Purchase_Order)
2012	21	15000
2012	22	24000
…..	…….	……….
2013	01	15000
2013	02	24000
2013	03	16000
……	….	….
2013	24	103560
2013	25	563200

could you please help me...

Regards

Chriss

Re: Need help in set expression

Wed, 19 Jun 2013 12:15:07 GMT

if(year=max(year) and month=max(month),sum(Purchase_Order))

Re: Need help in set expression

alexandros17 — Wed, 19 Jun 2013 12:15:33 GMT

Sum({$ <WEEK={'$(=Week(Now()))',year={'$(=year(Now())),'}>} Purchase_Order)

hope it helps

Re: Need help in set expression

Wed, 19 Jun 2013 12:17:41 GMT

Aggr(if( month=max(month),sum(Purchase_Order)),year)

Re: Need help in set expression

Wed, 19 Jun 2013 12:55:55 GMT

Hi Vishwa,

Thanks for yor reply, both condtions which are not working

=Num(Sum({<WEEK={"$(=max({<YEAR={"$(=max(YEAR))"}>}WEEK-1))"}>}Purchase_Order),'# ##0'), but it takes the total of all weeks, the aim is I would like to show the last week value only in a pivot table.

Hope I am clear to you.

Regards

Chriss

Re: Need help in set expression

prodanov — Wed, 19 Jun 2013 13:12:55 GMT

Hi,

This may be will help you.

Regards

Re: Need help in set expression

kumarnatarajan — Wed, 19 Jun 2013 13:42:10 GMT

Hi,

1. First Add "Year" Dimention.

2. Second Add calculated dimention Below script

=aggr(max(Week),Year)

3. Then add Expression

=Sum(Purchase)

Find the attached file.

Re: Need help in set expression

prodanov — Wed, 19 Jun 2013 13:45:43 GMT

Hi, results are not corrected!

Re: Need help in set expression

kumarnatarajan — Wed, 19 Jun 2013 13:53:23 GMT

Hi,

Use this expression.

Ex: if(Aggr(NODISTINCT max(Week),Year)=max(Week) and Aggr(NODISTINCT max(Year),Year)=max(Year),Purchase)

Find the attached file.