topic Sum of Values based on date field in App Development https://community.qlik.com/t5/App-Development/Sum-of-Values-based-on-date-field/m-p/1796790#M64403 <P>Hello all,&nbsp;</P><P>For the below table, I intend to use an KPI measure to show MTD Sales. But the MTD Sales should show the summary of values until last week. For getting the last week data, I use the expression =if(getselectedcount(Date)=0,num(WeekStart(today()-7)),Date). But I want to use this expression to get the MTD Sales on the KPI measure for last week.</P><P>Could you please help me with an expression to get the sum of MTD Sales for last week for the KPI measure</P><P>Thank you</P><TABLE><TBODY><TR><TD>Product Group</TD><TD>Week Number</TD><TD>Year</TD><TD>Month</TD><TD>Date</TD><TD>MTD Sales Units</TD></TR><TR><TD>A Box</TD><TD>W01 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2019-12-29</TD><TD>41</TD></TR><TR><TD>A Box</TD><TD>W02 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2020-01-05</TD><TD>146</TD></TR><TR><TD>A Box</TD><TD>W03 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2020-01-12</TD><TD>284</TD></TR><TR><TD>A Box</TD><TD>W04 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2020-01-19</TD><TD>410</TD></TR><TR><TD>A Box</TD><TD>W05 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2020-01-26</TD><TD>629</TD></TR><TR><TD>A Box</TD><TD>W06 2020</TD><TD>2020</TD><TD>Feb 2020</TD><TD>2020-02-02</TD><TD>169</TD></TR><TR><TD>A Box</TD><TD>W07 2020</TD><TD>2020</TD><TD>Feb 2020</TD><TD>2020-02-09</TD><TD>289</TD></TR><TR><TD>A Box</TD><TD>W08 2020</TD><TD>2020</TD><TD>Feb 2020</TD><TD>2020-02-16</TD><TD>533</TD></TR><TR><TD>A Box</TD><TD>W09 2020</TD><TD>2020</TD><TD>Mar 2020</TD><TD>2020-02-23</TD><TD>189</TD></TR></TBODY></TABLE><P>&nbsp;</P> Sat, 16 Nov 2024 17:19:10 GMT Arvind_07 2024-11-16T17:19:10Z Sum of Values based on date field https://community.qlik.com/t5/App-Development/Sum-of-Values-based-on-date-field/m-p/1796790#M64403 <P>Hello all,&nbsp;</P><P>For the below table, I intend to use an KPI measure to show MTD Sales. But the MTD Sales should show the summary of values until last week. For getting the last week data, I use the expression =if(getselectedcount(Date)=0,num(WeekStart(today()-7)),Date). But I want to use this expression to get the MTD Sales on the KPI measure for last week.</P><P>Could you please help me with an expression to get the sum of MTD Sales for last week for the KPI measure</P><P>Thank you</P><TABLE><TBODY><TR><TD>Product Group</TD><TD>Week Number</TD><TD>Year</TD><TD>Month</TD><TD>Date</TD><TD>MTD Sales Units</TD></TR><TR><TD>A Box</TD><TD>W01 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2019-12-29</TD><TD>41</TD></TR><TR><TD>A Box</TD><TD>W02 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2020-01-05</TD><TD>146</TD></TR><TR><TD>A Box</TD><TD>W03 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2020-01-12</TD><TD>284</TD></TR><TR><TD>A Box</TD><TD>W04 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2020-01-19</TD><TD>410</TD></TR><TR><TD>A Box</TD><TD>W05 2020</TD><TD>2020</TD><TD>Jan 2020</TD><TD>2020-01-26</TD><TD>629</TD></TR><TR><TD>A Box</TD><TD>W06 2020</TD><TD>2020</TD><TD>Feb 2020</TD><TD>2020-02-02</TD><TD>169</TD></TR><TR><TD>A Box</TD><TD>W07 2020</TD><TD>2020</TD><TD>Feb 2020</TD><TD>2020-02-09</TD><TD>289</TD></TR><TR><TD>A Box</TD><TD>W08 2020</TD><TD>2020</TD><TD>Feb 2020</TD><TD>2020-02-16</TD><TD>533</TD></TR><TR><TD>A Box</TD><TD>W09 2020</TD><TD>2020</TD><TD>Mar 2020</TD><TD>2020-02-23</TD><TD>189</TD></TR></TBODY></TABLE><P>&nbsp;</P> Sat, 16 Nov 2024 17:19:10 GMT https://community.qlik.com/t5/App-Development/Sum-of-Values-based-on-date-field/m-p/1796790#M64403 Arvind_07 2024-11-16T17:19:10Z Re: Sum of Values based on date field https://community.qlik.com/t5/App-Development/Sum-of-Values-based-on-date-field/m-p/1796791#M64404 <P>You could try an expression like this:</P><P>Sum({&lt; WeekNumber, Year, Month,[Date]={"&gt;=$(=MonthStart(today(),-1))&lt;$(=weekstart(today()))"}&gt;}[MTD Sales Units])</P> Mon, 05 Apr 2021 05:15:42 GMT https://community.qlik.com/t5/App-Development/Sum-of-Values-based-on-date-field/m-p/1796791#M64404 Vegar 2021-04-05T05:15:42Z Re: Sum of Values based on date field https://community.qlik.com/t5/App-Development/Sum-of-Values-based-on-date-field/m-p/1796798#M64406 <P>is it possible to get the above expression with Weekstart(today()-7) function alone? because my calendar month start and end dates are different than normal calendar</P> Mon, 05 Apr 2021 06:31:31 GMT https://community.qlik.com/t5/App-Development/Sum-of-Values-based-on-date-field/m-p/1796798#M64406 Arvind_07 2021-04-05T06:31:31Z