topic Re: getting Weekly max Date's value by set analysis in QlikView

getting Weekly max Date's value by set analysis

Mon, 15 Jun 2015 04:45:12 GMT

Dear Team/

i want to get the weekly max date value in table by using set analysis. like

each week have 4-5 working days. on each week i have to identify the max date of that week and get the value on that week.

below is the screenshot

Expression: Sum({<[Trading Date]={'$(=max([Trading Date]))'}>} [Close Rate])

Re: getting Weekly max Date's value by set analysis

sushil353 — Mon, 15 Jun 2015 05:07:56 GMT

Try using AGGR

=AGGR(Sum({<[Trading Date]={'$(=max([Trading Date]))'}>} [Close Rate]),Weekno)

HTH

sushil

Re: getting Weekly max Date's value by set analysis

help4qv123 — Mon, 15 Jun 2015 05:11:30 GMT

Bifurcate date into week as you do for month,year,q1 etc......one you get the week part which contain w1,w2,w3,w4

then use set analysis as u did, just in place of Trading date mention your week field like., Week=max(week)

Re: getting Weekly max Date's value by set analysis

Mon, 15 Jun 2015 05:28:02 GMT

That is also not working i used join for that. while loading putting a flag value on each max date of particular record.

Re: getting Weekly max Date's value by set analysis

Mon, 15 Jun 2015 05:40:53 GMT

Hi Jitendra,

Not sure how you can achieve this in expression, but in script you can do this.

LOAD

max(Date) as Date,

[Week no] as weekno

FROM

............ group by [Week no];

left join(X)

load

Date,

sales

FROM

......

Re: getting Weekly max Date's value by set analysis

Mon, 15 Jun 2015 05:44:28 GMT

Yes,

i had already done in that way only

Re: getting Weekly max Date's value by set analysis

aveeeeeee7en — Mon, 15 Jun 2015 05:45:39 GMT

First Create week Numbers using Week() Function.

After this, Create 2 Variables:

1. vMaxWeek
=Max(Week_NewDate)

2. vMaxDate
=Max(NewDate)

Call these 2 Variables into your Expression:

=SUM({<Week_NewDate={'$(vMaxWeek)'},NewDate={'$(vMaxDate)'}>}Amount)

After doing this, you'll get the desired Result.

Also, go through the Attachment.

Regards
Av7eN

Re: getting Weekly max Date's value by set analysis

Anonymous — Mon, 15 Jun 2015 05:46:07 GMT

sum({<[Trading Date]= $(vWeek >}[Close Rate])

vWeek = max(TradingDate).

.This expression worked for me with my own sample sample data .

Hope you might get some idea .

Thanks

Re: getting Weekly max Date's value by set analysis

Mon, 15 Jun 2015 06:09:56 GMT

That is not working in my case because i have more dimension to use in my table

Re: getting Weekly max Date's value by set analysis

Mon, 15 Jun 2015 06:10:03 GMT

That is not working in my case because i have more dimension to use in my table

Re: getting Weekly max Date's value by set analysis

aveeeeeee7en — Mon, 15 Jun 2015 06:13:02 GMT

Try something like this:

LOAD
Week_NewDate,
Max(NewDate) AS Date_New,
SUM(Amount) AS Amount_New
GROUP BY Week_NewDate;

LOAD *,
Week(NewDate) AS Week_NewDate;

LOAD *,
NUM(RAND()*1000,'#,##0.0') AS Amount;

Dates:
LOAD
date(makedate(2015,05,01)+Recno()-1,'DD/MM/YYYY') as NewDate
AUTOGENERATE 46;

Regards

Av7eN

Re: getting Weekly max Date's value by set analysis

Mon, 15 Jun 2015 06:20:59 GMT

Thanks to all of you who ever help me in this. i have already resolve this issue by using join at script level

Re: getting Weekly max Date's value by set analysis

Mon, 15 Jun 2015 06:26:18 GMT

Close the discussion with marking helpful or correct answer.