topic Re: Count cases from past week in QlikView

Count cases from past week

ElsaJulia — Wed, 25 Nov 2020 16:16:04 GMT

Hi. I am new to QlikView and need som help with a script or an expression.

I have created an Excel spreadsheet that contains rows with information about cases I am currently working on. In column A I have a unique ID-number, then a column with start date , a column with a status that can be active, resting, denied, queue or finished and then some columns with other information.

In my QlikView app I have created a tab that I have named "present situation" and there I have chosen to display cases that have the status active, resting or queue. I have also created boxes that displays a number which shows how many cases that have the status active, resting or queue. However - now I want to create a box that shows how many new cases that have been received during the last week. These new cases can have the status active, resting or queue but they can also have the status denied. My question now is how to create this box? What is the expression (and where do I put it) to calculate all new cases that have been received during the last seven days (from present day) - or even better if I some how could choose a week number and get a correct number. Also - in my tab I have (as I wrote) put that I only want to display cases that have the status active, resting och queue - and that goes for all different charts and boxes displayed on that tab except this box that I want to create since this particular box can also show cases that are also denied.

I really hope that I have described my problem correct and that anyone can help me solve it.

Re: Count cases from past week

ramachandra_gee — Mon, 04 Mar 2019 04:25:03 GMT

Hi,

Could you please share some sample data and expected out put.

Thanks,

Ram.

Re: Count cases from past week

Shubham_Deshmukh — Mon, 04 Mar 2019 09:32:45 GMT

ElsaJulia,

Create 2 variables as it is good practice when you deal with dates, you can create variables in setting->variable overview,

//vWeekStart=date(DayStart(today(),-7)) -> gives you weekStart from today (Today - 7)

//vToday=date(today()) -> gives today's date

Then just use below exp

=count({<startDate ={">=$(=vWeekStart)<=$(=vToday)"},status={ 'active', 'resting', 'queue'}>} ID)

Regards

Re: Count cases from past week

ElsaJulia — Mon, 04 Mar 2019 09:41:23 GMT

Hi. That´s it. In the attached picture I have taken a screenshot of three out of five boxes. Each of the blue boxes count ongoing cases, cases that are in queue, cases that are resting and a box that count the total. In the yellow empty box though, I would like to count cases that have been submitted during the last week. The text on the box translates (from swedish) to "New cases week X" where I also would like to show the week number of this present week instead of the X.

I have a trigger on my sheet characteristics that says to only display cases that are ongoing, in queue or resting - but in this particular yellow box I would also like to show denied cases (status denied) since I want to show all new cases that have been submitted this particular week regardless of its status.

Also - how do I put the specific week number in my time stamp? Now I use the code = Timestamp(Now()) but it would be really great if I could complete my present time stamp with the text "week" and then the week numer...

Re: Count cases from past week

Shubham_Deshmukh — Mon, 04 Mar 2019 09:50:51 GMT

You got my first answer?

You can get weekNumber by using Week(today())

Re: Count cases from past week

ElsaJulia — Mon, 04 Mar 2019 10:26:46 GMT

Thanx for your answer. I didn't get the code to work though (but I also have zero experience from coding).

I created the two variables and then in the text objects text box (tab text) I wrote: =count({<startDate ={">=$(=vWeekStart)<=$(=vToday)"},Status={ 'Pågående', 'Vilande', 'Kö', 'Nekat'}>} [K-nummer])

But everything from startDate to Status is underlined with red (which I guess is not ok). Also ID (which I replaces wit [K-nummer] which is the headline of my ID-column is all in red (but that maybe should be that way?).

Re: Count cases from past week

ElsaJulia — Mon, 04 Mar 2019 10:28:27 GMT

Hi again. Where do I put that code? In the text object for the yellow box I have in the text box written = Timestamp(Now()). Where do I write Week(today())? Sorry for my ignorance...

Regards

Re: Count cases from past week

Shubham_Deshmukh — Mon, 04 Mar 2019 10:33:51 GMT

Instead of startDate, put your dateField.
=count({<yourDateField={">=$(=vWeekStart)<=$(=vToday)"},Status={ 'Pågående', 'Vilande', 'Kö', 'Nekat'}>} [K-nummer])

Re: Count cases from past week

Shubham_Deshmukh — Mon, 04 Mar 2019 10:38:13 GMT

Where you want to show time stamp? You just use exp lile,

='Week - ' & week(today()) // gives you output Week - 10

Re: Count cases from past week

ElsaJulia — Mon, 04 Mar 2019 13:28:46 GMT

Hi again. I don´t get the code to work :(. Can you spot where the errors are

=count({<'Datum för inkommen begäran' ={">=$(=vWeekStart)<=$(=vToday)"},Status={ 'Pågående', 'Vilande', 'Kö', 'Nekat'}>} [K-nummer])

'Datum för inkommen begäran' is the column name for my start date column and 'K-nummer' is the column name for my ID-numbers.

Thanx!

Re: Count cases from past week

Shubham_Deshmukh — Mon, 04 Mar 2019 13:41:10 GMT

Still getting syntax error (red lines) or output is not coming?

Do not use ('), otherwise syntax is fine,

=count({<'Datum för inkommen begäran' ={">=$(=vWeekStart)<=$(=vToday)"},Status={ 'Pågående', 'Vilande', 'Kö', 'Nekat'}>} [K-nummer])

Re: Count cases from past week

Shubham_Deshmukh — Mon, 04 Mar 2019 13:38:46 GMT

Have you added values in variables?

Re: Count cases from past week

ElsaJulia — Mon, 04 Mar 2019 13:44:18 GMT

Hi. I added the variables as you did:

I get the result wrong (and my code is underlined with red:

Re: Count cases from past week

Shubham_Deshmukh — Mon, 04 Mar 2019 13:47:04 GMT

Do not use ('), otherwise syntax is fine,

=count({<'Datum för inkommen begäran' ={">=$(=vWeekStart)<=$(=vToday)"},Status={ 'Pågående', 'Vilande', 'Kö', 'Nekat'}>} [K-nummer])

Paste below expression,

=count({<Datum för inkommen begäran ={">=$(=vWeekStart)<=$(=vToday)"},Status={ 'Pågående', 'Vilande', 'Kö', 'Nekat'}>} [K-nummer])

Re: Count cases from past week

ElsaJulia — Mon, 04 Mar 2019 13:54:57 GMT

Hi.

It still doesn´t work. Now I get an error message (in the text box)...

Re: Count cases from past week

Shubham_Deshmukh — Mon, 04 Mar 2019 14:01:33 GMT

What as error?

It is working fine in my app

Re: Count cases from past week

ElsaJulia — Mon, 04 Mar 2019 14:12:04 GMT

Maybe there is something wrong with the swedish letters?

If I write my code as you suggested the whole line is underlined with red:

but if I put my column name that include swedish letters in [] or "", the red underline disappeares at least, but the result I get is wrong (0 where it should be 2).

Re: Count cases from past week

Shubham_Deshmukh — Tue, 05 Mar 2019 05:30:16 GMT

Ya, when you have spaces in between fields, you need to put them in [].
I don't know why it is not working in your case, it is working fine in my case.
Can you check whether you have data in past 7 days with those statuses?

Also check, any of filters (if any) are not selected.

Re: Count cases from past week

ElsaJulia — Fri, 08 Mar 2019 15:15:44 GMT

Hi again. It seems as if my latest reply magically disappeared :(. Thanks for your reply. Your solution worked! It was me who had made a misstake in my original file.

I however now have a new problem. I have several textboxes that show the number of for instance ongoing cases and now also the number of cases received during the last seven days (which you helped me with). I also have a chart that shows my original data. That is columns with some more information about a case (from my original files). If I for example cklick on the number in the textbox representing ongoing cases, the chart only shows information about these cases. I however don´t get this to work for my textbox representing the number of cases received during the last seven days.

I guess I have to add another field in the activity tab (in the text object properties) but I don´t know how. The other boxes work, but there I only have one activity linked to the box. Its only dependent on one column (status) in my original files. How can I solve this problem?

Re: Count cases from past week

Shubham_Deshmukh — Tue, 12 Mar 2019 10:05:28 GMT

Hi Elsa,

Kudos for first requirement !!!

Just go to Properties of text box->Action -> Add->select in field-> In Fields, put your date field(hope you have date field in table also, in search string put this ='>='&Date(WeekStart(today())) & '<=' &date(today()) - > Apply

When you will click on text box it will apply filter of date range on table data.