topic Re: Calculating the SUM based on MAX(Date) in QlikView

Calculating the SUM based on MAX(Date)

Fri, 11 Jan 2013 14:21:52 GMT

Hi All,

I have following data -

Product	Location	Date	Sales
A	Z	01-01-2013	100
A	Z	01-01-2013	100
A	Z	31-11-2012	200
B	Y	12-12-2012	300

1. I want to identify MAX(Date) group by location id.

2. Then calculate the SUM(Sales) based on MAX(Date) returned by step 1.

The result should be as follows -

Product	Sales
A	200	Since 01-01-2013 is the max date in location Z
B	300

Please help me how I can achieve this in straight table.

Regards,

Samir Prasadi.

Re: Calculating the SUM based on MAX(Date)

thomduvi83 — Fri, 11 Jan 2013 14:40:13 GMT

You can try this as expression in your table :

Sum(aggr(

FirstSortedValue(aggr(

Sum(Sales)

,Product,Location,Date),-Date)

,Product))

Br.

Re: Calculating the SUM based on MAX(Date)

Fri, 11 Jan 2013 15:10:13 GMT

Hi,

This is not working inmy application.

Regards,

Samir

Re: Calculating the SUM based on MAX(Date)

CELAMBARASAN — Fri, 11 Jan 2013 15:16:43 GMT

Are you expecting this in load script or charts?

Re: Calculating the SUM based on MAX(Date)

Fri, 11 Jan 2013 15:19:42 GMT

In the Chart.

Re: Calculating the SUM based on MAX(Date)

Fri, 11 Jan 2013 15:23:06 GMT

I tried the following expression in the chart expression -

SUM(

IF(

AGGR(MAX(DATE)), LOCATION) = DATE, SALES

)

but this does not work ..

Re: Calculating the SUM based on MAX(Date)

CELAMBARASAN — Fri, 11 Jan 2013 15:28:56 GMT

Can you upload a sample file that you are working with?

Re: Calculating the SUM based on MAX(Date)

Fri, 11 Jan 2013 15:50:04 GMT

I have attached a sample app.

Regards,

Samir

Re: Calculating the SUM based on MAX(Date)

CELAMBARASAN — Fri, 11 Jan 2013 16:04:49 GMT

Please check the attached file

Re: Calculating the SUM based on MAX(Date)

Tue, 15 Jan 2013 06:01:20 GMT

Hi,

Thanks for your response. This is working fine on the test file but the logic is not working on my production dashboard. Is there any other way to get solution for this scenario ?

Regards,

Samir.