Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

getting Weekly max Date's value by set analysis

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])

Scrip.png

12 Replies
sushil353
Master II
Master II

Try using AGGR

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

HTH

sushil

help4qv123
Creator II
Creator II

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)

Not applicable
Author

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

Not applicable
Author

Hi Jitendra,

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

X:

LOAD

max(Date) as Date,

[Week no] as weekno

FROM

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

left join(X)

load

Date,

sales

FROM

......

Not applicable
Author

Yes,

i had already done in that way only

aveeeeeee7en
Specialist III
Specialist III

Hi

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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