topic Re: Expression help. in QlikView

Expression help.

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

Below expression is not working...how can I put it together ?

SUM(IF(StartdateYear = year(Today()) ,

IF(EndtdateYear = YEAR(Today()),(ExpenseAmountLocal * NoOfMonths)) ,

(Sum(if(StartdateYear=year(Today()),

ExpenseAmountLocal*NoOfMonthsCY))

Sum(if(EndtdateYear=year(Today()),

ExpenseAmountLocal*NoOfMonthsCY)))))

Basically what I am looking for is if startyear and Endyear has same year (filter to currentyear)

then (ExpenseAmountLocal * NoOfMonths) else

if startyear and Endyear has different months filter to curentyear for both startyear and Endyear then (ExpenseAmountLocal*NoOfMonthsCY)

Thank you very much.

Re: Expression help.

sunny_talwar — Wed, 08 Feb 2017 14:42:51 GMT

Not sure I understand fully... would you be able to show by means of an example?

Re: Expression help.

Anonymous — Wed, 08 Feb 2017 14:46:50 GMT

Hi Ansh,

Have you tried moving the SUM to the fields individually?

Something like

=IF(StartdateYear = year(Today()) ,

IF(EndtdateYear = YEAR(Today()),(sum(ExpenseAmountLocal) * sum(NoOfMonths))) ,

if(StartdateYear=year(Today()),

sum(ExpenseAmountLocal)*sum(NoOfMonthsCY)))

if(EndtdateYear=year(Today()),

sum(ExpenseAmountLocal)*sum(NoOfMonthsCY))

Re: Expression help.

buzzy996 — Wed, 08 Feb 2017 14:53:00 GMT

can u try tis way,

=SUM(IF(StartdateYear = year(Today()) and EndtdateYear = YEAR(Today()),(ExpenseAmountLocal * NoOfMonths)) ,ExpenseAmountLocal*NoOfMonthsCY)

Re: Expression help.

Anonymous — Wed, 08 Feb 2017 15:00:06 GMT

since you are looking for Similar Month-year or Different Month-Year

I think you should work on Month-year field , not on only "Year "

Re: Expression help.

apthansh — Wed, 08 Feb 2017 15:02:43 GMT

Thank you Sunny and Shiva...I used below expression and looks like it is working so far ,but have to review with the team yet....I have set StartdateCY and EnddateCY to current year in script itself.

sum(if(StartdateCY=EnddateCY,ExpenseAmountLocal*NoOfMonths,

if(StartdateYear=year(today()),ExpenseAmountLocal*NoOfMonthsCY,

if(EndtdateYear=year(today()),ExpenseAmountLocal*NoOfMonthsCY))))