Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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))
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 !
May be if you can share a screenshot or a sample, I might have a better idea. Else I am truly out of ideas
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
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))
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
Hahahaha sure... take your time
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.
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))