Calculating last value by date/status

[#1]

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.

[Kushal_Chawda]

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)

[MindaugasBacius]

Please find the qvw attachment.

The results looks like this:

[tamilarasu]

Hi Ken,

Have a look at the attached file.

[#5]

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