topic Re: Last year march value in Last YTD in QlikView

Last year march value in Last YTD

qlikcheepirishe — Wed, 25 Nov 2020 16:16:04 GMT

My requirement is to get the last ytd

In last ytd it should display last year march value for the ytd below is the expression I am getting correct ytd can anyone guide me for the value to display on LAST YTD should display March value

YTD Expression=AVG(AGGR(AVG({1<FOR_DATE = {">=$(=DATE(YEARSTART(v_Date, 0, 4), 'DD/MM/YYYY'))"}, FOR_DATE = {"<=$(v_Date)"}>}Actual), Unit, KPI, UOM, Area, [Sub Area], Equipment))

Variables Used

v_Date=DATE(MAKEDATE(v_Max_Year, v_Max_Month), 'DD/MM/YYYY')

v_Max_Year==MAX(Year)

v_Max_Month =IF(GetSelectedCount(Month) <= 0, MAX(NUM_MONTH), NUM_MONTH) @

Re: Last year march value in Last YTD

Anonymous — Sat, 19 May 2018 09:27:56 GMT

Hi,

Try this:

AVG({<Year={'$(vPriorYear)'},Month=,Day=,v_Date={'<=$(vPriorYearDate)'}>}Actual_Unit)

vPriorYear=MAX(Year) -1

Thanks,

Re: Last year march value in Last YTD

Anil_Babu_Samineni — Sun, 20 May 2018 05:26:08 GMT

May be this?

AVG(AGGR(AVG({1<FOR_DATE = {">=$(=DATE(AddYears(YEARSTART(v_Date, 0, 4),-1), 'DD/MM/YYYY')) <=$(v_Date)"}>}Actual), Unit, KPI, UOM, Area, [Sub Area], Equipment))

Re: Last year march value in Last YTD

qlikcheepirishe — Mon, 21 May 2018 04:38:47 GMT

it is displaying ytd value which already i have been displaying.

i want when user selects 2018.

then should reflect last year march (total ytd of previous year)value I m using financial year

Re: Last year march value in Last YTD

jyothish8807 — Mon, 21 May 2018 05:45:21 GMT

Try this:

AVG(AGGR(AVG({1<FOR_DATE = {">=$(=DATE(AddYears(YEARSTART(v_Date, 0, 4),-1), 'DD/MM/YYYY')) <=$(=DATE(AddYears(v_Date,-1),DD/MM/YYYY'))"}>}Actual), Unit, KPI, UOM, Area, [Sub Area], Equipment))

Br,

Re: Last year march value in Last YTD

qlikcheepirishe — Mon, 21 May 2018 06:10:12 GMT

not giving exact output somewhere near you are jyothish kc

Re: Last year march value in Last YTD

jyothish8807 — Mon, 21 May 2018 06:20:17 GMT

Hi Ramesh,

What is your max(num_month) ?

Why do you want to use YEARSTART(v_Date, 0, 4) ? YTD should start from 1st Jan, here it will start from 1st April.

Br,

Re: Last year march value in Last YTD

jyothish8807 — Mon, 21 May 2018 06:24:34 GMT

Also what is the output of vDate ?

Re: Last year march value in Last YTD

qlikcheepirishe — Mon, 21 May 2018 06:45:21 GMT

as per the expression of jyothish now i want to freeze the values.

means on any click event the value should b tact.

for example: bypass for the expression

Re: Last year march value in Last YTD

jyothish8807 — Mon, 21 May 2018 06:52:32 GMT

Change your variable like this then,

v_Max_Month =MAX({1}total NUM_MONTH)

v_Max_Year =MAX({1}total Year)

Re: Last year march value in Last YTD

qlikcheepirishe — Mon, 21 May 2018 06:56:14 GMT

01/04/2018 = output for v_Date

Max(NUM_MONTH) = 4

my requirement is basis on financial year wise so using(v_Date,0,4) which starts from 1st April

Re: Last year march value in Last YTD

qlikcheepirishe — Mon, 21 May 2018 07:08:32 GMT

due to variable change it is affecting other expressions such as ytd actual etc.

any other way for output

Re: Last year march value in Last YTD

jyothish8807 — Mon, 21 May 2018 07:35:39 GMT

But did the variable change worked for last YTD ?

Br,

Re: Last year march value in Last YTD

jyothish8807 — Mon, 21 May 2018 08:11:34 GMT

Try this:

AVG(AGGR(AVG({1<FOR_DATE = {">=$(=DATE(AddYears(YEARSTART(v_Date, 0, 4),-1), 'DD/MM/YYYY')) <=$(=DATE(AddYears(v_Date,-1),DD/MM/YYYY'))"},Year=,NUM_MONTH=>}Actual), Unit, KPI, UOM, Area, [Sub Area], Equipment))

Re: Last year march value in Last YTD

qlikcheepirishe — Mon, 21 May 2018 09:32:13 GMT

Re: Last year march value in Last YTD

Anil_Babu_Samineni — Mon, 21 May 2018 09:38:04 GMT

You can define same for outer analysis like

AVG({1<FOR_DATE = {">=$(=DATE(AddYears(YEARSTART(v_Date, 0, 4),-1), 'DD/MM/YYYY')) <=$(v_Date)"}>} NODISTINCT AGGR(AVG({1<FOR_DATE = {">=$(=DATE(AddYears(YEARSTART(v_Date, 0, 4),-1), 'DD/MM/YYYY')) <=$(v_Date)"}>}Actual), Unit, KPI, UOM, Area, [Sub Area], Equipment))

If nothing - I would suggest you to paste sample?