ySales calculation based on xSales

[#1]

Hi All,

I have the two years of monthly data for 2015 and 2016.

When 2016 selected should show below results in two text boxes:

xSales=360 (sum of xSales from JAN-16 to AUG-16)

ySales=400 (sum of xSales from JAN-16 to AUG-16)

When 2015 selected should show below results:

xSales=705 (sum of xSales from JAN-15 to DEC-15)

ySales=860 (sum of ySales from JAN-15 to DEC-15)

DATA:

load *,

year(Month) as Year,Month(Month) as MonthName;

Load date#(Month,'MMM-YY') as Month,

Date(date#(Month,'MMM-YY')) as Date,

xSales,ySales inline [

Month,xSales,ySales

JAN-15,5,15

FEB-15,10,25

MAR-15,20,35

APR-15,30,45

MAY-15,40,55

JUN-15,50,65

JUL-15,60,75

AUG-15,70,85

SEP-15,80,95

OCT-15,100,105

NOV-15,110,125

DEC-15,130,135

JAN-16,10,15

FEB-16,20,25

MAR-16,30,35

APR-16,40,45

MAY-16,50,55

JUN-16,60,65

JUL-16,70,75

AUG-16,80,85

SEP-16,,95

OCT-16,,105

NOV-16,,125

DEC-16,,135

];

SalesDate:

load max(Month) as MaxDate

Resident DATA

where len(xSales)<>0

Order by Month desc;

Let vMaxDate=peek('MaxDate',0,'SalesDate');

[sunny_talwar]

Try these two expressions:

=Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(RangeMin(Max(Date), MaxDate)))"}>} xSales)

=Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(RangeMin(Max(Date), MaxDate)))"}>} ySales)

[sunny_talwar]

Just to add one little piece, you might want to add this to you code, if you would like to see YTD based on your selection in Month or MonthName fields also

=Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(RangeMin(Max(Date), MaxDate)))"}, Month, MonthName>} xSales)

=Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(RangeMin(Max(Date), MaxDate)))"}, Month, MonthName>} ySales)

[veidlburkhard]

...or try the easy version:

xSales: Sum(xSales)

ySales: Sum(Aggr(If(Sum(xSales) > 0, ySales), MonthName))

happy qliking

Burkhard

[jagan]

Sunny, not sure why you are using & to concatenate the expression, you can just try

=Sum({<Date = {">=$(=Date(YearStart(Max(Date))) <=$(=Date(RangeMin(Max(Date), MaxDate)))"}>} xSales)=Sum({<Date = {">=$(=Date(YearStart(Max(Date)))<=$(=Date(RangeMin(Max(Date), MaxDate)))"}>} ySales)

Regards,

Jagan.

[sunny_talwar]

Hi Jagan -

Its a great point, but I always use & function within the dollar sign expansion. The reason i do that is because, if every I want to check the range and/or format I am using within my set analysis, I can pick everything in between my dollar sign expansion and put it in a text box to see what I am getting. Its just saving myself from the trouble of working on it when I move this to a text box object. I know there are alternatives to do this, but this has become a habit and I find it very convenient to work with

Best,

Sunny

[jagan]

Oh ok, this is not that much readable. 

[jagan · Accepted solution]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[jagan]

Expressions:

='Xsales = ' & Sum({<Date={'<=$(=Date(vMaxDate))'}>}xSales)

='Ysales = ' & Sum({<Date={'<=$(=Date(vMaxDate))'}>}ySales)

Regards,

Jagan.