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!

[srini]

I hope now it is clear! I am not getting the logic to get the YTD with data available in

[sunny_talwar]

First thing first, how exactly are you loading the data into your app? Having the data in source is one thing, but you could have done data manipulation (such as The Crosstable Load). Without knowing how the data is loaded and what all fields are available, I am not sure what to tell you to do.

[srini]

it is loaded as direct not the cross table one. Model: LOAD Country, Category, "M01 Units", "M02 Units", "M03 Units", "M04 Units", "M05 Units", "M06 Units", "M07 Units", "M08 Units", "M09 Units", "M10 Units", "M11 Units", "M12 Units" // $(Vmonth) as Month, // if(Category="Actual",sum(if(Month='Mar'],"M01 Units","M02 Units"))) FROM [lib://Model/Model file.xlsx] (ooxml, embedded labels, header is 3 lines, table is Model);

[sunny_talwar]

Then if that is how you want to keep things, why can't you just sum all the months to find YTD?

Sum([M01 Units]) + Sum([M02 Units]) + Sum([M03 Units]) + ....... + Sum([M12 Units])

[srini]

But, my expression should only take till the current month... if it takes all the YTD for forecast will not be correct

[srini]

it should calculate next month data when I refresh the data next month only.

in the attached excel it has been simply done by using SUMIF.... I don't know how it can be done in Qlik sense 😞

[sunny_talwar]

You will get new data with Month 13, Month 14, and so on as columns? If that is true, then you need to consider using CrossTable load to make it easy

[srini]

Hi Sunny,

thanks for the reply,

I have loaded the data as cross table. 

But, I would like to get the sum of month data based on the current month. 

i.e. if I am refreshing the app with April 2019 Data I need to get the data sum of only M01 Units, M02 Units, M03 Units and M04 Units(particularly for Forecast).... but right now I am getting total of all month units in the during the sum. If I use Set analysis I need to go to expression each month and need to include the current month in the expression. Is there any possibility to achieve this. 

now I am using this expression:

sum({$<Month={'M01 Units','M02 Units'}>}[Units])

Thanks in advance!

[sunny_talwar]

Add Month as your dimension and then use the same expression.... this will give you sum by each month.

[srini]

Hi Sunny,

I can get the month wise sum. But my requirement is different. 

When I refresh the data on April month I should get the sum till April.

right now I am using 

Sum(<[Month]={"<$(=Month(Today()))"}>}[Units])

but which is not providing me the sum till this month