Count cases from past week

[ElsaJulia]

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.

[Shubham_Deshmukh]

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

[Shubham_Deshmukh]

Have you added values in variables? 

[ElsaJulia]

Hi. I added the variables as you did:

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

[Shubham_Deshmukh]

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

[ElsaJulia]

Hi.

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

E

[Shubham_Deshmukh]

What as error?

It is working fine in my app

[ElsaJulia]

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

[Shubham_Deshmukh]

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.

[ElsaJulia]

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?

[Shubham_Deshmukh]

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.