topic Re: Need Current Year value based on the Last Year value also in QlikView

Need Current Year value based on the Last Year value also

Thu, 11 Sep 2014 06:38:59 GMT

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

Re: Need Current Year value based on the Last Year value also

ajaykumar1 — Thu, 11 Sep 2014 06:48:44 GMT

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

Re: Need Current Year value based on the Last Year value also

Thu, 11 Sep 2014 07:14:11 GMT

Hello Ajay,

Thanks for the response,

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

Thanks

Re: Need Current Year value based on the Last Year value also

MK_QSL — Thu, 11 Sep 2014 07:51:25 GMT

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

Re: Need Current Year value based on the Last Year value also

Thu, 11 Sep 2014 10:31:11 GMT

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!

Re: Need Current Year value based on the Last Year value also

vikasmahajan — Thu, 11 Sep 2014 10:47:55 GMT

PFA document will help you to achieve the same

Vikas

Re: Need Current Year value based on the Last Year value also

Thu, 11 Sep 2014 14:30:33 GMT

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

Re: Re: Need Current Year value based on the Last Year value also

Thu, 11 Sep 2014 14:32:41 GMT

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

Re: Need Current Year value based on the Last Year value also

MK_QSL — Thu, 11 Sep 2014 15:10:15 GMT

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 ?

Re: Need Current Year value based on the Last Year value also

Thu, 11 Sep 2014 15:24:18 GMT

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

Re: Need Current Year value based on the Last Year value also

Thu, 11 Sep 2014 15:27:08 GMT

If value is less than 95% in both years then only i consider the value, other wise i should ignore the value

Re: Need Current Year value based on the Last Year value also

MK_QSL — Thu, 11 Sep 2014 15:29:31 GMT

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