Count amount of times a certain field value occurs during picked dates?

[Anonymous]

Hello,

Sorry for the long title. Its my first timeposting. Ill try to do better next time

I want to count the number of times a certain value occur between dates.
I have a field called CATEGORY that can either be 'SUCCESS' or 'UNSUCCESS' for delivery orders. So if there is no date selected then it should show for, example, delivery order number 123: 8 times of UNSUCCESS.

And if then I choose an end date where there had only been measured 'UNSUCCESS' a total of 4 times, it should count 4 times.

I have tried some SET but with no luck

count({<CategoryGraph={'UNSUCCESS'}>} distinct(Date))

And this is the result. Pic 1 & 2 is correct. However if I pick a date this is the result in Pic3. I know I can just choose all the prior days in the Date selector to get them all. But the request is to just be able to pick an end date, meaning select only one day.

Any ideas?

Thanks!

[sunny_talwar]

May be try this:

Count({<CategoryGraph={'UNSUCCESS'}, Date = {"$(='<=' & Date(Max(Date), 'YYYY-MM-DD'))"}>} distinct(Date))

[Anonymous]

Thanks!! I was looking at a solution like that but couldn't quite make it work. This works very well. But what if I want to see a selection of dates? Like if the first date of UNSUCCESS is 2016-12-01 and there has been an UNSUCCESS every day until 2016-12-10, and I select 2016-12-03 to 2016-12-06. Is there a good solution for this as well?

[sunny_talwar]

So are you saying that at times you can select just one date and other time you can select a range? May be like this:

If(GetSelectedCount(Date) <= 1,

Count({<CategoryGraph={'UNSUCCESS'}, Date = {"$(='<=' & Date(Max(Date), 'YYYY-MM-DD'))"}>} distinct(Date)),

Count({<CategoryGraph={'UNSUCCESS'}, Date = {"$(='>=' & Date(Min(Date), 'YYYY-MM-DD')) & '<=' & Date(Max(Date), 'YYYY-MM-DD'))"}>} distinct(Date)))

Basically when nothing or 1 date is selected, look at the what we initially had, but if more than one date is selected, then show between min and max date selected. Does this look like what you want?

[Anonymous]

Again, very good answer that solved it for me! But they have changed the request (again..) to only count the days of unsuccess if yesterday was an unsuccess. I tried this:

If(CategoryGraph='UNSUCCESS' and Date=(Date(Today()-1)),

Count({<Date = {"$(='<=' & Date(Max(Date), 'YYYY-MM-DD'))"}>} distinct(Date)),

0)

My date field already only shows workdays so not sure how Today() applies there.. Also have created a PriorWorkDay field that selects the day before Date.

Anyway, is the SET above on the right path?

[sunny_talwar]

This has gotten slightly more complicated and I feel I might be able to help you better if you are able to provide a sample with expected output

[Anonymous]

Sorry for the delayed reply. Here is the file. The last tab Aging.. is where I have done the set

[sunny_talwar]

I am looking at this, but I am not sure what the output needs to look like? For the selections you have already made, would you be able to point out what the expected output needs to be in terms of numerical values?