9 Replies Latest reply: Nov 25, 2016 8:11 AM by Sunny Talwar

Sum if date equals max date

Hi guys,

I'm back again

In Sheet A I have a table.

My first problem was solved with this help   Re: Get Max Date Ignoring dimension

Now I have another problem.

Example of context:

I have a store, and times to times I count all af my products, I do an inventory of my stock.

so I have a count at 01-06-2016:

product A - 15

product B - 30

And another count at day 30-06-2016

Product A - 24

Product B - 0

so in my table I want my max date equals to 30-06-2016 (solved above)

And sum of quantity for product A equal to 24 and for product B equal to 0

Because at 30-06-2016 there was no stock for product B.

Did you understand my problem?

I'm trying this, but its not working :

Sum({<RecordType={"Stocks"}, Date={"=\$(=Date(Max(total <StoreCode, ProductCode> {<RecordType = {"Stocks"}>} Date), 'YYYY-MM-DD'))"}>} Quantity)

Thanks in advance for any help.

Regards,

Sílvia

• Re: Sum if date equals max date

I've solved half of the problem with this:

if(Date = Date(Max(total <StoreCode> {<RecordType = {"Stocks"}>} Date), 'YYYY-MM-DD'),

Sum({<RecordType={"Stocks"}, Date={"=\$(=Date(Max(total <StoreCode> {<RecordType = {"Stocks"}>} Date), 'YYYY-MM-DD'))"}>} Quantity),

0

)

But the line in yellow above is only showing if I put the dimension date on the table. But I want that line to appear without that dimension on it.

What am I missing?

• Re: Sum if date equals max date

Hi again,

I was abble to reach some results, but it's still not wotking

I've attached the qvf app with the current result, and an csv file.

In the csv file, the two last columns, are what I want to achieve, for each StoreCode and ProductCode.

I appreciate any help.

Thanks,

Sílvia

• Re: Sum if date equals max date

Which sheet am I looking at? Sheet A or Sheet B?

• Re: Sum if date equals max date

Hi Sunny,

SheetA

• Re: Sum if date equals max date

Are the numbers in the csv file expected output or what is the expected output?

• Re: Sum if date equals max date

The expected output is the last two columns of the csv file: quantityExpected and MaxDate Expected.

Thanks.

• Re: Sum if date equals max date

Try this:

Sum(Aggr(If(Date = Max(total <StoreCode> {<RecordType = {"Stocks"}>} Date),

Sum(Quantity), 0), StoreCode, ProductCode, Date))

• Re: Sum if date equals max date

Thanks Sunny, it worked

• Re: Sum if date equals max date

Awesome