topic Simple YTD in QlikView

Simple YTD

parpatra — Sun, 31 Oct 2010 23:59:25 GMT

Month	Lob	Amount	YTD
1/1/2010	A	10	10
1/1/2010	B	15	15
1/1/2010	C	20	20
2/1/2010	A	25	35
2/1/2010	B	30	40
2/1/2010	C	35	55
3/1/2010	A	40	75
3/1/2010	B	45	85
3/1/2010	C	50	105

The YTD column is the expected result. Can you please help me with the expression using the sample data.

Simple YTD

jonathandienst — Mon, 01 Nov 2010 06:07:50 GMT

Use this script (modified to fit your data):


LOAD
 TranDate,
 Product,
 Qty,
 If (Product = Previous(Product) AND Year(TranDate) = Year(Previous(TranDate)), RangeSum(Peek('YTD'), Qty), Qty) AS YTD,
 If (Product = Previous(Product) AND Month(TranDate) = Month(Previous(TranDate)), RangeSum(Peek('YTD'), Qty), Qty) AS MTD
RESIDENT tmpData
ORDER BY Product, TranDate
;

See attached for an example.

Hope that helps

Jonathan

Simple YTD

parpatra — Mon, 01 Nov 2010 16:17:32 GMT

Thanks Jonathan...

But I was trying to handle it in an expression...is that possible?

Simple YTD

johnw — Mon, 01 Nov 2010 21:59:25 GMT

Perhaps this?

rangesum(Amount,if(year(Month)=year(above(Month)),above(YTD)))

I generally try to avoid using above(), though, as it depends on the exact way the chart is displayed and sorted. I prefer a data solution. I'd create an AsOf table. See the below post for a similar question and my suggested solution.

http://community.qlik.com/forums/t/36252.aspx

Simple YTD

parpatra — Tue, 02 Nov 2010 18:27:47 GMT

Can I use SET analysis to show YTD on Bar chart using the above data set?

I was able to put it in a pivot without any dimension, but it fails when we add a dimension.

=sum({<Month={"<=Month">}>} Amount) (Syntax might be incorrect)

Also I dnot want to have full accumulation option to be selected. Since when I select only 'feb' it should show me just one bar with FEB YTD value, which is not the case in full accumulation.

Thanks!!

Simple YTD

johnw — Tue, 02 Nov 2010 20:09:05 GMT

You can't use set analysis for this because the set is only calculated once, not once for each row (each Month in this case) in your chart.