Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikcheepirishe
Creator
Creator

Last year march value in Last YTD

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) @

15 Replies
Anonymous
Not applicable

Hi,

Try this:

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

vPriorYear=MAX(Year) -1

Thanks,

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
qlikcheepirishe
Creator
Creator
Author

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

jyothish8807
Master II
Master II

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,

KC

Best Regards,
KC
qlikcheepirishe
Creator
Creator
Author

not giving exact output somewhere near you are jyothish kc

jyothish8807
Master II
Master II

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,

KC

Best Regards,
KC
jyothish8807
Master II
Master II

Also what is the output of vDate ?

Best Regards,
KC
qlikcheepirishe
Creator
Creator
Author

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

jyothish8807
Master II
Master II

Change your variable like this then,

v_Max_Month =MAX({1}total NUM_MONTH)

v_Max_Year =MAX({1}total Year)

Best Regards,
KC