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.

[ramachandra_gee]

Hi,

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

Thanks,

Ram.

[Shubham_Deshmukh]

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

[ElsaJulia]

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

[Shubham_Deshmukh]

You got my first answer?

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

[ElsaJulia]

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

[ElsaJulia]

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

[Shubham_Deshmukh]

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

[Shubham_Deshmukh]

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

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

[ElsaJulia]

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!