topic Re: Re: How to SUM for YTM ? in QlikView https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486629#M1140167 <HTML><HEAD></HEAD><BODY><P>Ehm, have one more Q related - the user is selecting instead the MonthID - the MonthName which is a non-numeric field <IMG src="https://community.qlik.com/legacyfs/online/emoticons/confused.png" /></P><P>I`m struggling to adapt the logic to the MonthName.</P></BODY></HTML> Thu, 05 Sep 2013 12:29:11 GMT martin_cevela 2013-09-05T12:29:11Z How to SUM for YTM ? https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486623#M1140156 <HTML><HEAD></HEAD><BODY><P>Hi there,</P><P></P><P>Is there a way to SUM for YTM? I need to compare the sum of revenue for current year with the sum of forecast from begin of the year until the month selected. Examples found are only returning the SUM for the selected month and not the SUM for YTM. I`m new to QV and have found X ways that claim YTM solution, but unfortunately none of them works for me…</P><P></P><P style="margin-bottom: .0001pt;"><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: blue;">num</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">(</SPAN></P><P style="margin-bottom: .0001pt;"><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: blue;">SUM</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">({ &lt;</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: maroon;">Year</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">={'$(CurrentYearID)'}&gt; } </SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: maroon;">AmountEURFixed</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">)</SPAN></P><P style="margin-bottom: .0001pt;"><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">/</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: blue;">sum</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">({&lt;</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: maroon;">Year</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">={'$(CurrentYearID)'},</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: maroon;">MonthID</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;"> = {'&lt;=$(CurrentMonthID)'}&gt;} </SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: maroon;">ForecastEURFixed</SPAN><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">)</SPAN></P><P style="margin-bottom: .0001pt;"><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: black;">,'#,##%') <BR /></SPAN></P><P></P><P>This expression returns the SUM for selected month (AmountEURFixed &amp; ForecastEURFixed) or if no value selected then the Current Year SUM for AmountEURFixed &amp; YTM SUM for ForecastEURFixed. However, what would be the amendment need to get the dynamic SUM for YTM?</P><P></P><P>Thanks for any help here!</P><P style="margin-bottom: .0001pt;"><SPAN style="font-size: 14.0pt; font-family: 'Courier New'; color: blue;"><BR /></SPAN></P></BODY></HTML> Thu, 05 Sep 2013 09:45:28 GMT https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486623#M1140156 martin_cevela 2013-09-05T09:45:28Z Re: How to SUM for YTM ? https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486624#M1140157 <HTML><HEAD></HEAD><BODY><P>Please share your sample app.</P></BODY></HTML> Thu, 05 Sep 2013 10:03:03 GMT https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486624#M1140157 tresB 2013-09-05T10:03:03Z Re: How to SUM for YTM ? https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486625#M1140158 <HTML><HEAD></HEAD><BODY><P>Finally managed to figure out how to upload my example...</P></BODY></HTML> Thu, 05 Sep 2013 10:33:07 GMT https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486625#M1140158 martin_cevela 2013-09-05T10:33:07Z Re: Re: How to SUM for YTM ? https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486626#M1140159 <HTML><HEAD></HEAD><BODY><P><BR />May be like this? PFA.</P></BODY></HTML> Thu, 05 Sep 2013 10:55:11 GMT https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486626#M1140159 tresB 2013-09-05T10:55:11Z Re: How to SUM for YTM ? https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486627#M1140160 <HTML><HEAD></HEAD><BODY><P><STRONG>Exactly like this! Thanks for your time Treseco. </STRONG><STRONG><BR /></STRONG><SPAN lang="EN-US" style="line-height: 115%; font-family: 'Calibri','sans-serif'; font-size: 11pt; mso-ansi-language: EN-US; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-fareast-language: EN-US; mso-bidi-language: AR-SA;">I have found this expression and natively removed the date part...</SPAN></P><P>So, I got the fish, but want to learn how to catch too (if you can spare one more reply), and hence<BR />would like to understand why the "Date" is needed here although data<BR />is aggregated to months (coming out of SQL world...)?<STRONG><BR />&nbsp; <BR />&nbsp; <BR /> <BR /> <BR /> <BR /><BR /> </STRONG></P><P><STRONG><BR /><BR /><BR /><BR /></STRONG></P></BODY></HTML> Thu, 05 Sep 2013 11:25:46 GMT https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486627#M1140160 martin_cevela 2013-09-05T11:25:46Z Re: How to SUM for YTM ? https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486628#M1140162 <HTML><HEAD></HEAD><BODY><P>If you don't include Date, then also you can get the desired result, but then you have to bypass the Date field selection and write the set expression accordingly. With the above solution you have the fexlibility to get the dynamic YTD as well.</P></BODY></HTML> Thu, 05 Sep 2013 11:37:21 GMT https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486628#M1140162 tresB 2013-09-05T11:37:21Z Re: Re: How to SUM for YTM ? https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486629#M1140167 <HTML><HEAD></HEAD><BODY><P>Ehm, have one more Q related - the user is selecting instead the MonthID - the MonthName which is a non-numeric field <IMG src="https://community.qlik.com/legacyfs/online/emoticons/confused.png" /></P><P>I`m struggling to adapt the logic to the MonthName.</P></BODY></HTML> Thu, 05 Sep 2013 12:29:11 GMT https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486629#M1140167 martin_cevela 2013-09-05T12:29:11Z Re: Re: How to SUM for YTM ? https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486630#M1140169 <HTML><HEAD></HEAD><BODY><P><BR />ok - sorry for being so dummy <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" /></P><P>This is the amendment I was looking for....</P><P><SPAN style="color: #0000ff; font-size: 8pt;">num</SPAN><SPAN style="font-size: 8pt;">(</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">Sum</SPAN><SPAN style="font-size: 8pt;">({&lt;</SPAN><SPAN style="color: #800000; font-size: 8pt;">Year</SPAN><SPAN style="font-size: 8pt;">={'$(CurrentYearID)'},</SPAN><SPAN style="color: #800000; font-size: 8pt;">MonthID</SPAN><SPAN style="font-size: 8pt;">={</SPAN><SPAN style="color: #800000; font-size: 8pt;">"&lt;=$(=Max(MonthID))"</SPAN><SPAN style="font-size: 8pt;">}, </SPAN><SPAN style="color: #800000; font-size: 8pt;">MonthName</SPAN><SPAN style="font-size: 8pt;">=&gt;}</SPAN><SPAN style="color: #800000; font-size: 8pt;">AmountEURFixed</SPAN><SPAN style="font-size: 8pt;">)<BR />/</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">Sum</SPAN><SPAN style="font-size: 8pt;">({&lt;</SPAN><SPAN style="color: #800000; font-size: 8pt;">Year</SPAN><SPAN style="font-size: 8pt;">={'$(CurrentYearID)'},</SPAN><SPAN style="color: #800000; font-size: 8pt;">MonthID</SPAN><SPAN style="font-size: 8pt;">={</SPAN><SPAN style="color: #800000; font-size: 8pt;">"&lt;=$(=Max(MonthID))"</SPAN><SPAN style="font-size: 8pt;">}, </SPAN><SPAN style="color: #800000; font-size: 8pt;">MonthName</SPAN><SPAN style="font-size: 8pt;">=&gt;}</SPAN><SPAN style="color: #800000; font-size: 8pt;">ForecastEURFixed</SPAN><SPAN style="font-size: 8pt;">), '#,##%') </SPAN></P><P></P></BODY></HTML> Thu, 05 Sep 2013 12:40:10 GMT https://community.qlik.com/t5/QlikView/How-to-SUM-for-YTM/m-p/486630#M1140169 martin_cevela 2013-09-05T12:40:10Z