Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please find the below front end expression & let me know, how to optimize this or provide me better way. This is taking longer time to load application & some time error occurred (expression tome out).
Purpose of this --- when I selecting the one date , I want to take the items of Year to last Month end date (YTLM) average sales <=100 (Exclude month no 9 & 12 sales).
=if(((sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}-{'9','12'}
//,WEEK = {"<=$(=max({<YEAR={$(=max(YEAR))}>} WEEK))"}
,QUARTERNO = {"<=$(=max({<YEAR={$(=max(YEAR))}>} QUARTERNO))"}
,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE)-
(count({$<YEAR = {$(=max(YEAR))}
,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))>$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}
,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE))"}
>} distinct DAY)))"}
>}SECONDARY_INVOICE_QTY))
/
(count({$<YEAR = {$(=max(YEAR))} ,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}
>}MONTH)))>=0 and
((sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}-{'9','12'}
//,WEEK = {"<=$(=max({<YEAR={$(=max(YEAR))}>} WEEK))"}
,QUARTERNO = {"<=$(=max({<YEAR={$(=max(YEAR))}>} QUARTERNO))"}
,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE)-
(count({$<YEAR = {$(=max(YEAR))}
,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))>$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}
,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE))"}
>} distinct DAY)))"}
>}SECONDARY_INVOICE_QTY))
/
(count({$<YEAR = {$(=max(YEAR))} ,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}
>}MONTH)))<=100,
((sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}-{'9','12'}
//,WEEK = {"<=$(=max({<YEAR={$(=max(YEAR))}>} WEEK))"}
,QUARTERNO = {"<=$(=max({<YEAR={$(=max(YEAR))}>} QUARTERNO))"}
,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE)-
(count({$<YEAR = {$(=max(YEAR))}
,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))>$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}
,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE))"}
>} distinct DAY)))"}
>}SECONDARY_INVOICE_QTY))
/
(count({$<YEAR = {$(=max(YEAR))} ,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}
>}MONTH))),'0')
Thanks & Regards
Amila Fernando
HI Amila,
It's quite difficult to help when the only thing you post is the expression, and being that large it's even harder.
Can you share the app with some test data?
Felipe.
Hi Felip,
Sorry for the late reply & Thank you very much for your effort, I understood that the difficulty of this. So can you please let me know whether I can extract Last 12 months data when selecting the particular date?
Thanks
Amila
Hi,
As correctly write @felipedl it`s difficult to work with this formula without application model, but i have some advices, that may help you.
# 1. How YEAR, MONTH and QUARTERNO related to TRANSDATE in data? Did you use master calendar for dates? If yes, may be you need to filter only TRANSDATE field and comment filters of YEAR, MONTH and QUARTERNO as in the following example:
sum(
{$<
//YEAR = {$(=max(YEAR))},
//MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}-{'9','12'}
//,WEEK = {"<=$(=max({<YEAR={$(=max(YEAR))}>} WEEK))"}
//,QUARTERNO = {"<=$(=max({<YEAR={$(=max(YEAR))}>} QUARTERNO))"}
,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE)-
(count({$<YEAR = {$(=max(YEAR))}
,MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))>$(=max({<YEAR={$(=max(YEAR))}>} MONTH)-1)"}-{'9','12'}
,TRANSDATE = {"<=$(=max({<YEAR={$(=max(YEAR))}>} TRANSDATE))"}
>} distinct DAY)))"}
>}SECONDARY_INVOICE_QTY).
# 2. You can create separate month field 'SalesMonth' for example, that calculated by following formula:
IF( MonthNumber = 9 OR MonthNumber = 12, NULL(), MonthNumber )
And using this field may automatically exclude data with months 9 and 12, because it has NULL and not included in set analysis.
Of course this advice is not applicable if you configure these months from interface.
Hi Roman,
# 2. You can create separate month field 'SalesMonth' for example, that calculated by following formula:
IF( MonthNumber = 9 OR MonthNumber = 12, NULL(), MonthNumber )
***Thanks for your reply & the above was helpful to avoid months.
Hi,
If anyone know how to take the last 12 months (up to last month end date) average sales when selecting the one date, please publish the expression. Below is my calendar with period.
Calender:
Load
DATE(TRANSDATE) as TRANSDATE,
YEAR(ADDMONTHS(TRANSDATE,-3)) AS YEAR,
TEXT(MONTH(TRANSDATE)) AS MONTHLINK,
Week(ADDMONTHS(TRANSDATE,-3)) as WEEK,
Day(TRANSDATE) as DAY,
WeekDay(BILL_TO_CUSTOMER) as WEEKDAY
Resident Stock;
Concatenate
LOAD
Date(Date) as TRANSDATE,
YEAR(ADDMONTHS(Date,-3)) AS YEAR,
TEXT(MONTH(Date)) AS MONTHLINK,
Week(ADDMONTHS(Date,-3)) as WEEK,
Day(Date) as DAY,
WeekDay(Date) as WEEKDAY
;
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;
Period:
LOAD * INLINE [
MONTHLINK, MONTH, QUARTER,Monthday,QUARTERNO
Apr, 1, Q1,1,1
May, 2, Q1,2,1
Jun, 3, Q1,3,1
Jul, 4, Q2,4,2
Aug, 5, Q2,5,2
Sep, 6, Q2,6,2
Oct, 7, Q3,7,3
Nov, 8, Q3,8,3
Dec, 9, Q3,9,3
Jan, 10, Q4,10,4
Feb, 11, Q4,11,4
Mar, 12, Q4,12,4
];
Thanks
Amila Fernando