topic Re: Calculate LMTD in QlikView

Calculate LMTD

Mon, 14 Oct 2013 10:35:42 GMT

I want to Calculate Last Month till Date Sales. I have Selected 12 Oct -13 . I want to Calculate 01-Sep to 11-Sep-13.. Can u Please Suggest me the query

Re: Calculate LMTD

Mon, 14 Oct 2013 10:47:40 GMT

Try this one,

date={'>=monthstart(max(date),-1)<MakeDate(year(max(Date)),Month(Max(Date))-1,day(max(Date)))'}

Re: Calculate LMTD

Mon, 14 Oct 2013 10:55:16 GMT

=Sum({<FinYear=,FinMonthName=,FinancialYear=,FinQuarter=, DateField={">=$(=Date(monthstart(vMaxDate,-1)))<=$(=Date(monthend(vMaxDate)))"}>}sales)

vMaxPrvDate=Date(max(CommonDateField),'DD/MM/YYYY')

Re: Calculate LMTD

salto — Mon, 14 Oct 2013 11:17:57 GMT

Hi,

maybe not the most elegant but I would try substracting the current month sales to to the total sales in the year, and then show the result:

=num(sum({<Year={$(=Max(Year))}> } SalesAmount))

num(sum({<Year={$(=Max(Year))}, MonthNum={$(=max(MonthNum)-1)} > } SalesAmount))

Hope this helps.

Re: Calculate LMTD

Mon, 14 Oct 2013 12:38:11 GMT

Hi,

Can U please explain the Expression. Because I have Date, Month , Year, Values Only

Re: Calculate LMTD

Mon, 14 Oct 2013 12:50:53 GMT

I think this expression using set analysis will work :

sum( {< date={">=$(=monthstart(max({1}date),-1)) <$(=max({1}date))"} >} SALES)

we select the date for which to calculate the sum of sales.

Regards

Stéphane