Solved: Re: Need Current Year value based on the Last Year... - Qlik Community

Report Inappropriate Content · ‎2014-09-11

Hello All,

I have a scenario,

I am showing one value, The value should satisfy two condition.

I have today, today my revenue is less than 95%

same day last year my revenue is less than 95%.

now i should consider this year value.

so i need to write a expression if year=current year and Revenue <95%, Year=PY and revenue<95% then Revenue for today

any help to write this condition

Thanks

MK_QSL · ‎2014-09-11

Create a Straight Table

Dimension = Month

Expression

IF(SUM({<Year = {'$(=Max(Year))'}>}Value) <0.95 and SUM({<Year = {'$(=Max(Year)-1)'}>}Value) <0.95, SUM({<Year = {'$(=Max(Year))'}>}Value))

View solution in original post

ajaykumar1 · ‎2014-09-11

Hi,

Just try

sum({<year ={"$(=(max(period)))"}>}revenue)------------------------Exp1

sum({<year ={"$(=(max(period)-1))"}>}revenue)----------------------Exp2

if(column(1)=column(2),column(1),'your expected output')---------Exp3

Regards,

Ajay

Report Inappropriate Content · ‎2014-09-11

Hello Ajay,

Thanks for the response,

but i have only one expression, i need to include all conditions in one expression

Thanks

MK_QSL · ‎2014-09-11

Better if dummy or sample data being provided to work...

Report Inappropriate Content · ‎2014-09-11

I would put a counter field into the time table, for example for the YearMonth. This should work with the autonumber (That is how I always use it), for example:

AutoNumber(YearMonth, 'PeriodID')

as [PeriodID]

Then you get an Integer value for every month and you can calculate the "vActualPeriodID". From this "vActualPeriodID" you just substract 12 (PeriodID<= ($(vActualPeriodID)-12) and you with this and the (actual) Year = ($(vActualYear) -1) you got two timeconditions that your record set has to match to get the last years data till the same month last year (LYTD - Last Yrar To Date).

Hope this works for u!

vikasmahajan · ‎2014-09-11

PFA document will help you to achieve the same

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

Report Inappropriate Content · ‎2014-09-11

Hello All,

PFA for the attached QVW and Excel, My requirement is below:

i have two years 2014, 2013

i have value by months

in values there are some places it is less than 95% for both years

i need a chart with month as a dimension

for measures i need to have two

first measure is when value is less than 95% in both 2014 and 2013 years then 2014 value only

second measure is when the value is less than 95% in 2013 then 2013 value.

Please help into this.

Thanks in advance

Report Inappropriate Content · ‎2014-09-11

Hello Manish,

PFA for the attached QVW and Excel, My requirement is below:

i have two years 2014, 2013

i have value by months

in values there are some places it is less than 95% for both years

i need a chart with month as a dimension

for measures i need to have two

first measure is when value is less than 95% in both 2014 and 2013 years then 2014 value only

second measure is when the value is less than 95% in 2013 then 2013 value.

Please help into this.

Thanks in advance

MK_QSL · ‎2014-09-11

Confusing...

let me clarify first...

1) Value < 95% in both 2013 and 2014, consider Value of 2014

2) Value < 95% in 2013 and >95 in 2014, consider Value of 2013....

What if both above condition not satisfied?

i.e.

Value > 95% in 2013 ?

Value > 95% in both 2013 and 2014 ?

Report Inappropriate Content · ‎2014-09-11

Hi Manish

Yes, I need to consider the value<95% in both years, then only i need to take current year value.

i have many years in list box, which ever year i select it should consider selected year and previous year of selected year.

Thanks

Need Current Year value based on the Last Year value also