topic Re: Calculating last value by date/status in QlikView

Calculating last value by date/status

Thu, 02 Jun 2016 05:20:15 GMT

Hey folks,

In the following example, I want to sum up the number of issues that ended up at status for a cyclic group containing date and month.

Script:

Data:

LOAD * Inline

[

Date, Sequence, Issue, Status

2016-06-01, 1, 100, Open

2016-06-01, 2, 100, In Progress

2016-06-02, 1, 100, Open

2016-07-02, 1, 100, Closed

2016-06-01, 1, 200, Open

2016-07-02, 1, 200, In Progress

2016-07-02, 2, 200, Closed

];

Left Join (Data)

LOAD

Month(Date) AS Month

Resident Data;

The resulting chart will have one of the two sets of values, depending on what the cyclic group is set to:

Cyclic Group = DateDate	Status	Sum
2016-06-01	In Progress	1
2016-06-01	Open	1
2016-06-02	Open	1
2016-07-02	Closed	2

Or:

Cyclic Group = Month	Status	Sum
Jun	Open	2
Jul	Closed	2

What's the best way to go about doing this?

Thanks,

Ken

Message was edited by: Ken van Mulder Missed adding line #7 of the script to include data for 2016-06-02.

Re: Calculating last value by date/status

Kushal_Chawda — Thu, 02 Jun 2016 09:07:04 GMT

like this

Data:

LOAD *, monthname(Date) as Monthyear Inline

[

Date, Sequence, Issue, Status

2016-06-01, 1, 100, Open

2016-06-01, 2, 100, In Progress

2016-07-02, 1, 100, Closed

2016-06-01, 1, 200, Open

2016-07-02, 1, 200, In Progress

2016-07-02, 2, 200, Closed

];

Now create the Cyclic group , add Date & MonthYear .

Create straight table

Dimension:

CyclicGroup

Status

Expression:

Count(Status)

Re: Calculating last value by date/status

MindaugasBacius — Thu, 02 Jun 2016 09:07:48 GMT

Please find the qvw attachment.

The results looks like this:

Re: Calculating last value by date/status

tamilarasu — Thu, 02 Jun 2016 09:10:12 GMT

Hi Ken,

Have a look at the attached file.

Re: Calculating last value by date/status

Thu, 02 Jun 2016 16:21:14 GMT

Hello,

Thanks for the answers - much appreciated, but unfortunately they're missing the core of the problem.

I only want to count the issue once for the status that it ends the selected time period. See the example tables for the expected output.

I've worked out a way to get it working using a calculated value for relevant record per time period, but I'm hoping there is a better solution.

I've attached a file showing the sample solution to date.

Thanks,

Ken