22 Replies Latest reply: Jul 10, 2017 8:11 AM by Eric Hannert

# count at max date with status

Hi comm,

I want to calculate the amount of artikel with a status under 70 so the formular is like this atm.

count(if(status<70,art_nr))

But I have different dates of the date so for example we have

3 articels with status under 70 at the 06.07.2017

and 1 articel with status under 70 but at the 7.7.2017

So I only want to count the 1 articel from the highest date which is the 7.7.2017.

Hope someone can help me, I tried different formulars but seem to miss something.

• ###### Re: count at max date with status

May be this

Count({<status = {'<70'}, Date = {"\$(=Date(Max(Date), 'D.M.YYYY'))"}>} art_nr)

• ###### Re: count at max date with status

wow thx a lot i changed it a bit so it fits the rest so its like this:

Count({<status = {'<70'}, beleg_dat = {"\$(=Date(Max(beleg_dat), 'hh:mm:ss DD/MM/YYYY'))"}>} art_nr)

• ###### Re: count at max date with status

Hi Sunny

sorry that I ask u again, but as I tested it and saw that it works but only when i selected an order and an orderposition.

i tried aggr on order and orderposition lvl but i didn't worked out. U have any ideas?

• ###### Re: count at max date with status

I think I need a max date for each orderposition. I try to do it but maybe u have an idea.

• ###### Re: count at max date with status

In that case, try this

Count(DISTINCT {<status = {'<70'} Aggr(If(Date = Max(TOTAL <art_nr, orderposition> Date), art_nr), art_nr, orderposition, Date))

• ###### Re: count at max date with status

I dont get it to work properly. I'm sorry. But I found there is a } missing in the end

but i dont find my mistake. Here is my code:

Count(DISTINCT {<status = {'<70'} Aggr(If(beleg_dat = Max(TOTAL <art_nr, ord_ipactype1> beleg_dat), art_nr), art_nr, ord_ipactype1, beleg_dat)})

I Think im missing a Date() in the aggr right?

• ###### Re: count at max date with status

Count(DISTINCT {<status = {'<70'}>} Aggr(If(Date = Max(TOTAL <art_nr, orderposition> Date), art_nr), art_nr, orderposition, Date))

• ###### Re: count at max date with status

should have found it myself!!!

But i have another question for u.

When i have multiple articles at the maxdate it doesnt count them! Everything is at 1.

Example

We have 12 articles at maxdate for order1 orderposition1 at 6/7/2017

and 2 at order 1 orderposition1 at 7/7/2017

i only get 1 but i should get the 2 from 7/7/2017

• ###### Re: count at max date with status

May be see if removing the distinct helps here

Count({<status = {'<70'}>} Aggr(If(Date = Max(TOTAL <art_nr, orderposition> Date), art_nr), art_nr, orderposition, Date))

• ###### Re: count at max date with status

Hi Sunny,

i tried it out but it only counts 1 for each position. From my understanding the formular is right, but i cant find the problem.

So here is my formular maybe I did a dumb copy mistake:

Count({<status = {'<70'}>} Aggr(If(beleg_date = Max(TOTAL <art_nr, ord_position1> beleg_date),ord_position1), art_nr, ord_position1, beleg_date))

• ###### Re: count at max date with status

Don't you want to count the art_nr instead of ord_position1?

Count({<status = {'<70'}>} Aggr(If(beleg_date = Max(TOTAL <art_nr, ord_position1> beleg_date),ord_position1), art_nr, ord_position1, beleg_date))

vs

Count({<status = {'<70'}>} Aggr(If(beleg_date = Max(TOTAL <art_nr, ord_position1> beleg_date),art_nr), art_nr, ord_position1, beleg_date))

• ###### Re: count at max date with status

yeah sorry i tried around a bit but this difference has no impact.

The last date is right but the count is wrong its allways 1. Makes no sense for !

• ###### Re: count at max date with status

May be if you can share a screenshot or a sample, I might have a better idea. Else I am truly out of ideas

• ###### Re: count at max date with status

I cant put in a direct example but I provide u some images.

thats the table: with company a number(not important) and the others in red are translations for better unterstanding.

And this is the table were we get the articles from. after art_nr we have the orderposition. Ord_docid is ordernumber.

and we have the orderstable. where u can see the orderid, orderposition and amount that should be in orderposition

• ###### Re: count at max date with status

May be this

Count({<status = {'<70'}>} Aggr(If(beleg_date = Max(TOTAL <art_nr, ord_position1> beleg_date),beleg_nr), art_nr, ord_position1, beleg_date))

or

Sum({<status = {'<70'}>} Aggr(If(beleg_date = Max(TOTAL <art_nr, ord_position1> beleg_date),1), art_nr, ord_position1, beleg_date, beleg_nr))

• ###### Re: count at max date with status

The sum() function seem the best option for me atm. Seems right .

But need some more testing from my side. I'll mark ur answer as helpfull.When its correct after testing u get the right answer mark. Thx for u help atm

• ###### Re: count at max date with status

Only thing i need to take care of now is to only use max date at status<70.

Cause without this I'll get a max date where an article is 70 and therefore a sum of 0.

• ###### Re: count at max date with status

I guess this

Sum({<status = {'<70'}>} Aggr(If(beleg_date = Max(TOTAL {<status = {'<70'}>} <art_nr, ord_position1> beleg_date),1), art_nr, ord_position1, beleg_date, beleg_nr))

• ###### Re: count at max date with status

doesn't seem right. I used it in main function and again only 0 values.

but i tried this at the date column and i get the lasted dates where status<70 is right:

aggr({<status = {'<70'}>} nodistinct max(beleg_dat),ord_ipactype1)

strange thing there is I get the right latest date but not the sum of these rows

• ###### Re: count at max date with status

let me check my data. cause this only happens in one row and only in this order I have the case that there are multiple record with status<70 and different dates. Atleast if have the right highest date of them. need to fix the sum calc now.

• ###### Re: count at max date with status

I must go now. I will work on this next monday. But when u have a solution share pls

I NEED UR HELP XD