Sum if date equals max date

[Anonymous]

Hi guys,

I'm back again

See the attached app please.

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

[Anonymous]

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?

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

Hi Sunny,

SheetA

[sunny_talwar]

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

[Anonymous]

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

Thanks.

[sunny_talwar · Accepted solution]

Try this:

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

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

[Anonymous]

Thanks Sunny, it worked

[sunny_talwar]

Awesome