How to use column(1) value in expression

[#1]

Dear Experts ,

In my report i want to count no of shipment and display origin in expression . Please look in to my qvw and attached image .

I want to count no of shipment for the last 6 months before the last job date , i,e my last job date is 21-Mar-2014 .

totally there is 5 shipments from last job  to last job - 180   (41539 to 41719) . also i want to show my origin value for the max job id 41719 .

Please help me .

[sunny_talwar]

I see only one shippment which falls under the Max(Job_ID) and Max(Job_ID)-180

If what I am saying is right, then this expression is working:

=Count({<Job_ID = {"$(='>=' & (Max(Job_ID)-180))"}>}NO_OF_SHIPMENT)

For origin, try this expression:

FirstSortedValue(Origin, Job_ID)

Output Table:

HTH

Best,

Sunny

[swuehl]

I believe the HELP is misleading that you are not just referencing the value of first column by using column(1), but you are referencing the expression. And since your expression is an aggregation function, you need advanced aggregation if you want to embed it in another aggregation.

maybe like attached.

[#4]

Thanks for your reply ,

Please select date in calendar (30-jun-15), totally there is 5 shipment between job id  41719 to 41539 . i tried with your solution , but it always showing single shipment for other customer also .

Also i want to show max job id origin value . Firstsorted value is taking min job id value .

kindly help me to solve .

[#5]

Dear Swuehl ,

Thanks a lot its working perfectly , but if i change date range , its taking more time to load , i think its happening because of huge data .

Just one small thing , if my last job date is null , then i want to hide customer row. i have selected suppress zero values in presentation for all expressions , but still service steel customer is appearing in report .

kindly look my image file and suggest .

[swuehl]

Can you limit your data to your current selection and upload the QVW?

[#7]

Dear Swuehl ,

i have attached qvw with sample customer . if my last job date is null , then i want to hide customer row.

Kindly suggest .

[jagan]

Hi Sriram,

PFA file for solution.

Regards,

Jagan.

[swuehl]

Attached should work.

[#10 · Accepted solution]

Dear Swuehl ,

If my count of max (job_id) is > 1 and origin or destination  have different value , then its not showing in out put .

For example customer Agritrade , max job is 41502 where destination have 2 different value , i want to display yangon in destination which displaying first in table box .

For customer 3m  india limited , both origin and destination value is null ,

Kindly suggest . Thanks in advance for your continued support .