topic Re: Non moving expression in QlikView

Non moving expression

amilafdo — Tue, 26 Jun 2018 06:03:21 GMT

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

Re: Non moving expression

felipedl — Tue, 26 Jun 2018 15:48:42 GMT

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.

Re: Non moving expression

amilafdo — Fri, 29 Jun 2018 05:39:05 GMT

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

Re: Non moving expression

romansavchuk — Sat, 30 Jun 2018 12:35:03 GMT

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.

Re: Non moving expression

amilafdo — Tue, 03 Jul 2018 04:28:50 GMT

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.

Re: Non moving expression

amilafdo — Tue, 03 Jul 2018 04:35:32 GMT

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