Qlik Sense count rows within last week

[Anonymous]

Hi all,

I am trying to make a KPI to count the number of errors occurring in the last week (InputType = Error). I thought this would be simple but I am having far more trouble than I should with it. I have been trying to use variations of =Count({<InputType = {'Error'}, Date = {'>=(Today()-7)'} >} InputType).

I have a feeling it not working may be due to different date formats, but I cannot get anything to work, even when trying to use date() and similar to convert the dates. It only seems to be summing Errors from all time and ignoring the date range.

Date	Version	Site	Error	InputType
2017-12-09	2.5	AAA	Things broke	Error
2017-12-09	2.3	SSS	0.74	Stat
2017-12-07	2.3	BBB	0.65	Stat
2017-11-31	2.1	AAA	Something went wrong	Error

Any help would be much appreciated.

Thanks

[sunny_talwar · Accepted solution]

Try this out

=Count({<InputType = {'Error'}, Date = {">=$(=Date(Today()-7, 'YYYY-MM-DD'))"}>} InputType).

[jonvitale]

Chris,

I suggest working this out step-by-step. You have a nice little table there. Try adding a measure to it: IF(Date > Today() - 7, 1, 0). Work out from there.

You may also want consider creating an extra field called DateNum, which would be Num(Date). This might make it easier to do comparisons.

Finally, in your load script, make sure that your DateFormat is 'YYYY-MM-DD'. It's not the default.

[Anonymous]

Ohhh that's how you convert the date.

Thank you very much, works perfrectly

[jonvitale]

Sunny, how's anyone suppose to get points around here with you answering all the questions?

[Anonymous]

Thank you for that, I think that would be a good practice to start and a good method to try things

[YoussefBelloum]

Hi,

you can try this:

=Count({<InputType = {'Error'}, Date ={"$(= '>=' &date(today()-7))"} >} InputType)

[sunny_talwar]

I am done for the day