Bar graph in cumulative format - expected vs actual

[sayadutt]

I have 2 tables as follows:

Table 1				Table 2
Month	Target			Month	Week	Achieved
Jan	50			Jan	Week1	10
Feb	45			Jan	Week2	20
				Jan	Week3	10
				Feb	Week1	10
				Feb	Week2	20
				Feb	Week3	10

Need is to create a line graph showing weekly progress across Target to Weekly achieved.

Note: Target is defined at Monthly level, whereas Achieved is at Week level.

looking for a line graph.

[pradosh_thakur]

dimension1 = Week

Dimension 2 = Month

Expression 1 = sum(Achieved)

Expression 2 = ONLY(Target)

regards

Pradosh

[sayadutt]

Does not look to be working. Achieved does not give the cumulative sum

[pradosh_thakur]

My bad.

try

expression1 =

rangesum(above(sum(Achieved),0,rowno()))

[sayadutt]

Expression 1 is working good. Problem is with Expression 2.
We need Expression 2 to also get cumulative based on Month (and not Week)

Currently Expression2 shows ONLY value.
Expression 2 = ONLY(Target)


Currently Expression 2 is gettig cumulative, but not Expression 1. Hence Achieved is coming greater than target.

[sayadutt]

Just to explain more:

From below table, TARGET is same across the month. Only ACHIEVED grows each week.

Requirement is to draw the cumulative line graph showing TARGET and ACHIEVED.

(Target should be same for a month, Achieve should grow each week)

month	Week Ending	Target	Achieved
Jan 19	12/21/2018	2	1
Jan 19	12/28/2018	2	0
Jan 19	1/4/2019	2	0
Jan 19	1/11/2019	2	1
Feb 19	1/18/2019	1	0
Feb 19	1/25/2019	1	1
Feb 19	2/1/2019	1	0
Feb 19	2/8/2019	1	0

[pradosh_thakur]

Hi

I was not able to reply back yesterday. Here is the solution to your issue. From the previous data you posted

load * inline [
Month,Target	 	 	
Jan,30 	 	
Feb,70
];
 	 	 	 	
 	 	 	 	
 	 	 	 	
 	 	 	 	

load *,Month &  chr(10) & Week as Month_Week inline [
Month,Week,Achieved
Jan,Week1,10
Jan,Week2,10
Jan,Week3,10
Feb,Week1,10
Feb,Week2,20
Feb,Week3,10
];

dimension =

Month_Week

Measure 1= rangesum(above(sum(Achieved),0,rowno())

measure 2= only(Target)

This is working for me. If you need a qvw/qvf let me know.

Regards

Pradosh

[sayadutt]

Hello Pradosh, can you share the qvw file as well. When I tried, this was not working as expected.
My expected graph which I am looking for is here:
https://community.qlik.com/t5/New-to-Qlik-Sense/Cumulative-line-graphs/m-p/1545330#M127316


Thanks a lot !!!

[sayadutt]

Hi, To explain better, let me change the data and you will see that it is not working as expected.I am using the same dimensions and expressions as given by you.load * inline [
Month,Target 
Jan,2 
Feb,1
];load *,Month & chr(10) & Week as Month_Week inline [
Month,Week,Achieved
Jan,Week1,1
Jan,Week2,0
Jan,Week3,0
Jan,Week4,1
Feb,Week1,1
Feb,Week2,0
Feb,Week3,0
];

The blue line should not cross red line (as achieved is never more than target in my case)

Expectation  - For each Month, the numbers are considered from 0 and has no relation with earlier month data. We are just trying to show Target vs Achieved each month

What I want:

What I am getting by above expressions: