Count by period using only 1 date

[AlexWest]

Hi, guys!

Is anyone can help me with the next task:

I have a table with "operation dates" and "names of the operations".

I have to chose one date in a filter (for example, 05.Jan.2023) and need to return a COUNT of "names of the operations" from the beggining of the month (in this case - from 01.Jan.2023 till 05.Jan.2023).

Or if I chose 26.Dec.2022, it needs to be returned a COUNT of "names of the operations" from 01.Dec.2022 till 26.Dec.2022.

Please help if anyone knows what I have to do to make it, because I don't know what I have to do in SCRIPT, and I don't know what CHART I have to use to show this data((

Thanks a lot in advance!!!

[Vegar · Accepted solution]

Assuming you [operation dates] field is recognised as a date you could solve this by using set analysis.

Try something like the expression  below for calculating you chart measure.

COUNT ({< [operation dates]={">=$(=MonthStart(max([operation dates])))<=$(=max([operation dates]))"}>} [names of the operations])

[AlexWest]

Hi, Vegar!

Thanks for your reply, but it doesn't work in my case)

I can see the folowing pictures:

 

 

[menta · Accepted solution]

I think Vegar solution is good, the only change YearStart beacuse you need value from the start of the year.

If you have also year or month selected you need to write in the Set analysis this exclusions

Count({< [operation dates]={">=$(=Date(YearStart(max([operation dates]))))<=$(=Date(max([operation dates])))"}, Year=, Month=>} [names of the operations])

[AlexWest]

Thanks! In the end I've got a needed result.

First I've made a mistake, when I wrote "Operation" field instead of Vegar's "Operation date" XD

But anyway, I first time see the form of expression with quotes signs. The expression looks like a text.

Is there any link to get more info about this part of aggregate funcs?

[Marcel_Garcia]

Hi AlexWest, check this out and like if helps 😉
Aggregation functions ‒ Qlik Sense on Windows