topic Re: ySales calculation based on xSales in QlikView

ySales calculation based on xSales

Wed, 25 Nov 2020 16:16:04 GMT

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');

Re: ySales calculation based on xSales

sunny_talwar — Mon, 10 Oct 2016 13:12:02 GMT

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)

Re: ySales calculation based on xSales

sunny_talwar — Mon, 10 Oct 2016 13:29:47 GMT

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)

Re: ySales calculation based on xSales

veidlburkhard — Mon, 10 Oct 2016 13:33:30 GMT

...or try the easy version:

xSales: Sum(xSales)

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

happy qliking

Burkhard

Re: ySales calculation based on xSales

jagan — Mon, 10 Oct 2016 13:48:44 GMT

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.

Re: ySales calculation based on xSales

sunny_talwar — Mon, 10 Oct 2016 13:51:58 GMT

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

Re: ySales calculation based on xSales

jagan — Mon, 10 Oct 2016 14:06:06 GMT

Oh ok, this is not that much readable.

Re: ySales calculation based on xSales

jagan — Tue, 11 Oct 2016 05:38:44 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: ySales calculation based on xSales

jagan — Tue, 11 Oct 2016 05:39:23 GMT

Expressions:

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

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

Regards,

Jagan.