topic Summation Of Amount for a date range in QlikView

Summation Of Amount for a date range

pkpandey — Wed, 31 Aug 2016 09:59:04 GMT

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.

Re: Summation Of Amount for a date range

sunny_talwar — Wed, 31 Aug 2016 10:04:35 GMT

May be this:

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

Re: Summation Of Amount for a date range

pkpandey — Wed, 31 Aug 2016 10:20:21 GMT

Thanks for your prompt reply.

Re: Summation Of Amount for a date range

Anonymous — Wed, 31 Aug 2016 10:46:49 GMT

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)

Re: Summation Of Amount for a date range

pkpandey — Thu, 01 Sep 2016 04:57:43 GMT

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

Re: Summation Of Amount for a date range

pkpandey — Thu, 01 Sep 2016 07:21:43 GMT

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

Re: Summation Of Amount for a date range

antoniotiman — Thu, 01 Sep 2016 07:53:26 GMT

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

Re: Summation Of Amount for a date range

sunny_talwar — Thu, 01 Sep 2016 10:36:29 GMT

What is the expected output for this new data provided?

Re: Summation Of Amount for a date range

sunny_talwar — Thu, 01 Sep 2016 10:38:06 GMT

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

Re: Summation Of Amount for a date range

pkpandey — Mon, 05 Sep 2016 10:30:26 GMT

Hi Antonio

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

Re: Summation Of Amount for a date range

pkpandey — Mon, 05 Sep 2016 10:31:13 GMT

Hi Sunny T

This is not the correct data from the expression you have given.