Summation Of Amount for a date range

[pkpandey]

Hi All

How to get the following output from the attached excel sheet.

Item        Amount

A01         550

A02         905

A03         554

Sum of only yellow colored cells i.e. amount between third max date and max date.

[sunny_talwar · Accepted solution]

May be this:

=Sum(Aggr(If(Dt >= Max(TOTAL <Item> Dt, 3), Amt), Item, Dt))

[pkpandey]

Thanks for your prompt reply.

[Anonymous]

Hi

Use this for calculating max and max 3

But I have got max date as 3/14/2016  and third max date as 2/16/2016  and amount would be :  784

SOURCE:

LOAD Item,

    Dt,

    Amt

FROM

(ooxml, embedded labels, table is Sheet1);

MAX_MIN:

LOAD

Max(Dt) as maxdate,

Max(Dt,3) as maxdate3

Resident SOURCE;

LET VMAX = Num(PEEK('maxdate',0,'MAX_MIN'));

LET VMAX3=PEEK('maxdate3',0,'MAX_MIN');

DROP TABLE MAX_MIN;

******************************************

And create PIVOT or straight table with this Expresssion :

sum( {1 < Dt={">=$(=date(VMAX3,'MM/DD/YYYY'))<=$(=date(VMAX,'MM/DD/YYYY'))"}  > }  Amt)

[pkpandey]

Hi Sunny

If there is any duplicate date then the output is not correct. I am attaching the excel sheet for your

information.

On Wed, 31 Aug 2016 15:35:35 +0530 Sunny T wrote

Summation Of Amount for a date range

reply from Sunny T in QlikView App Development - View the full discussion

May be this:=Sum(Aggr(If(Dt >= Max(TOTAL Dt, 3), Amt), Item, Dt))

https://community.qlik.com/servlet/JiveServlet/downloadImage/2-1115158-136113/Capture.PNG

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in QlikView App Development by email or at Qlik Community

Following Summation Of Amount for a date range in these streams:

Inbox

© 1993-2016 QlikTech International AB |

Copyright & Trademarks | Privacy | Terms of Use | Software EULA

[pkpandey]

its not like that. You have to take max date and 3rd max date item wise and should be dynamic means

depends on the selection of the user.

On Wed, 31 Aug 2016 16:17:34 +0530 Alluraiah Allu wrote

Summation Of Amount for a date range

reply from Alluraiah Allu in QlikView App Development - View the full discussion

HiUse this for calculating max and max 3But I have got max date as 3/14/2016 and third max date as

2/16/2016 and amount would be : 784SOURCE:LOAD Item, Dt,

AmtFROM(ooxml, embedded labels, table is

Sheet1);MAX_MIN:LOADMax(Dt) as maxdate,Max(Dt,3) as maxdate3Resident SOURCE;LET VMAX =

Num(PEEK('maxdate',0,'MAX_MIN'));LET VMAX3=PEEK('maxdate3',0,'MAX_MIN');DROP TABLE MAX_MIN;

>

>******************************************

>And create PIVOT or straight table with this Expresssion :sum( {1 < Dt=

{">=$(=date(VMAX3,'MM/DD/YYYY'))

[antoniotiman]

Hi Kumar,

try expression

RangeSum(FirstSortedValue(DISTINCT Aggr(Sum(Amt),Item,Dt),-Dt,3),FirstSortedValue(DISTINCT Aggr(Sum(Amt),Item,Dt),-Dt,2),FirstSortedValue(DISTINCT Aggr(Sum(Amt),Item,Dt),-Dt))

Regards,

Antonio

[sunny_talwar]

What is the expected output for this new data provided?

[sunny_talwar]

I am getting the same values as before with the new data that provided. Is that incorrect?

[pkpandey]

Hi Antonio

Thank you. The output from the expression you have given is accurate.