Hello,
I have a query which returns Planned_Sprint, Total Hours,Actual Hours,Team,Type. Now i want a stacked bar and line chart , in which the stacked bars are split by Planned_Sprint,Type and the line is only Split by Planned_Sprint.
The X-axis as Planned_Sprint, the Y as (Total Hours)stacked bar and (Actual Hours)line.
Planned_Sprint | Total Hours | Actual Hours | Type | Team |
Sp_1 | 10 | 20 | A | WWW |
Sp_1 | 15 | 10 | B | DDD |
Sp_1 | 5 | 10 | B | RRR |
Sp_2 | 10 | 15 | A | WWW |
Sp_2 | 20 | 5 | A | DDD |
Sp_2 | 10 | 10 | B | TTT |
Sp_2 | 5 | 8 | C | RRR |
Sp_3 | 20 | 20 | B | TTT |
Here is my code for the chart:
| chart sum(Total Hours) AS PLANNED_Hours,sum(Actual Hours) as AC by Planned_Sprint,Type
But in this even the AC gets split by Type. I do not want this, i want something like this:
| chart sum(Total Hours) AS PLANNED_Hours by Planned_Sprint,Type, sum(Actual Hours) as AC by Planned_Sprint
Any suggestion how this can be achieved?
| chart sum(Total Hours) AS PLANNED_Hours,sum(Actual Hours) as AC by Planned_Sprint,Type
| fillnull value=0
| eval AC=0
| foreach AC:*
[| eval AC=AC+'<<FIELD>>']
| fields - AC:*
| chart sum(Total Hours) AS PLANNED_Hours,sum(Actual Hours) as AC by Planned_Sprint,Type
| fillnull value=0
| eval AC=0
| foreach AC:*
[| eval AC=AC+'<<FIELD>>']
| fields - AC:*
@ITWhisperer I just figured, this works only if all or at least 2 "Type" are present. For the final row in my table above,where Planned Sprint = Spr_3,the Actual hours(AC) line does not come up as Spr_3 has only one Type unlike Spr_1 and Spr_2. Although there is data in Actual hours(AC) column.Any help here?
I have figured it out, the null values were causing problems. |Fornull value=0 solves it
Hi
you should do it something like
<your base search>
| stats sum('Total Hours') AS PLANNED_Hours sum('Actual Hours') as AC by Planned_Sprint
Then select visualisation as bar chart and then there is an Overlay option where you should select AC.
Unfortunately I'm not getting what you want to do with Type or is it even relevant?
r. Ismo
This will not work..The total hours are split by both Planned_Sprint and "Type" for each team.So the x axis becomes the Planned_Sprint and the y axis has two things: the sum of "Total Hours" split across various "Types" as stacked bars and the actual Hours Line graph should be just sum(Actual Hours ) split across Planned_Sprint
Hi Iso,
i just assumed that he wants to do:
Total Hours in case Type is A
Actual Hours in Case Type is B
basically he tried using by as a filter...i think
Hello,
I have edited my question with the sample table and expected chart
Hi,
the easiest way would be an append...i am sure that there are multiple solutions out there to get this in one Go....but why bother if it can be achieved by a simple "append"
Regards