need help

Thu, 12 Mar 2015 16:25:42 GMT

Hello experts,

I need current year sales and previous year sales with out making selections in year field.

A table which consists of ename, sales, and year field(2001-2015)

Then I want to make a report with ename(dim)

M1:

LOAD

ename,

sales,

year

FROM

C:\Users\admin\Desktop\QV\qv_recent.xlsx

(ooxml, embedded labels, table is Sheet1);

M2:

LOAD Max(year) as curyear

Resident M1;

LET Vcurdate= Peek('curyear');

Yes, it works fine for current year sales

Sum({$<year={$(Vcurdate)}>}sales)

But if I take previous sales its not working

Sum({$<year={$(Vcurdate)-1}>}sales)

I don't know where am wrong.

Any suggestions will be appreciated.

Re: need help

buzzy996 — Thu, 12 Mar 2015 16:51:38 GMT

try from front-exp using the below syntax.

=num(sum({$<Year = {$(=only(Year))}, Month = {"<=$(=max(Month))"}>} SalesAmount), '$#,##0;($#,##0)')

=num(sum({$<Year = {$(=only(Year) - 1)}, Month = {"<=$(=max(Month))"}>}SalesAmount), '$#,##0;($#,##0)')