YTD Calculation by column wise!!

[srini]

Hi Friends,

I am struggling to calculate the YTD for the below data in qlik Sense. In Excle simply they used SUMIF to calculate the YTD.

But when I try to get the same in qlik sense I am not able to get... I am struggling... I have mentioned the result YTD also in the below table. Can some one help it would be really helpful. 

I have tried with earlier posts in the community but most of them are for row wise... and not useful for my case. 

My mind is not working with some extend 😞

Country	M01 Units	M02 Units	M03 Units	M04 Units	M05 Units	M06 Units	M07 Units	M08 Units	M09 Units	M10 Units	M11 Units	M12 Units	YTD
XYZ	0	0	0.52903	0	0	0	0	0	0	0	0	0		0.52903
ABC	1	0	0	0	0	0	0	0	0	0	0	0		1
def	0	-1	0	0	0	0	0	0	0	0	0	0		-1
jkl	0.375	0	0	0	0	0	0	0	0	0	0	0		0.375
mnop	0.375	0	0	0	0	0	0	0	0	0	0	0		0.375
sdfg	0.375	0	0	0	0	0	0	0	0	0	0	0		0.375
erty	0.375	0	0	0	0	0	0	0	0	0	0	0		0.375
mnhu	0	0.255918	0.028435	0	0	0	0	0	0	0	0	0		0.284353
hytr	0	0	0.568707	0	0	0	0	0	0	0	0	0		0.568707

Thanks in advance!

[sunny_talwar]

I think this issue can be resolved much easily if you are able to share a sample where we can see what you have. 

[srini]

Hi Sunny,

Thanks! I have attached the QVF file similar to my data. My data can't be shared outside. 

My requirement is to get the sum of units "till" current month and that can saved as dimension in fact table. 

whatever I attached is working for current month sum. But I need it "till"  current month. I have used <= in the expression but is not giving any results. 

So, my ask here 

1) need to get the sum till current month(i.e. Jan, Feb, Mar and Apr)

2) once it is worked I need that to be added as new Field "YTD" in the fact table for other use.

Thanks Friend!

[srini]

I hope now it is clear now with attachment 🙂

[sunny_talwar]

I suggest creating a new field which is a number like this

Test1:
 Load
 Country,
 Category,
 Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM')) as [Month],
 Num(Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM'))) as [MonthNum],
 Units 
 Resident Test;
 Drop Table Test;

and then try this

Sum({<MonthNum = {"<=$(=Num(Month(Today())))"}>} Units)

[sunny_talwar]

It sure is. Thank you!!

[srini]

Great! fabulous! Thanks Sunny! Sum({} Units) as NEWField how could I use this as a field in different table?

[sunny_talwar]

Sum in script needs a group by statement... why do you want to do this in the script?

[srini]

Hi Sunny, As I said, this is going to be a input for another table. Hence need it in script as new field.

[sunny_talwar]

Can you provide few rows of input and the expected output from the input you are hoping to see?

[srini]

Hi Sunny, I hope it is there in the attachment with previous posts.