Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

21 Replies
sunny_talwar

May be this

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

Not applicable
Author

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)

Not applicable
Author

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?

Thx for an answer in advance

Not applicable
Author

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

sunny_talwar

In that case, try this

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

Not applicable
Author

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?

sunny_talwar

My bad, should be this

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

Not applicable
Author

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

sunny_talwar

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