Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
Please find the qvw attachment.
The results looks like this:
Hi Ken,
Have a look at the attached file.
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