topic Re: How to separate the years in a Straight Table in QlikView

How to separate the years in a Straight Table

umartareen — Tue, 22 Jul 2014 07:20:17 GMT

Hi I'm a newbie in QlikView. Please help me find the expression or code that is used to sum the date for two different years separately and show it through a Straight Table. To have a better picture of what I'm trying to do, please find the files attached.

Thanks in Advance.

Re: How to separate the years in a Straight Table

aveeeeeee7en — Tue, 22 Jul 2014 07:33:14 GMT

Firstly create an year field from Backend Script like this:

LOAD State,

Plant,

Date,

Year(Date) AS Year,

Amt

FROM

....Desktop\EXE1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Then, Create 2 expressions in Straight Table:

Amt Current Year:

Sum({< Year = { $(=Max(Year(Today()))) } >} Amt)

Amt Last Year:

Sum({< Year = { $(=Max(Year(Today()))-1) } >} Amt)

It's good if you create variables for vMaxyear=Max(Year(Today()))) and vPrevyear=Max(Year(Today()))-1) then use it in your expressions:

Amt Current Year:

Sum({< Year = { $(=vMaxyear} >} Amt)

Amt Last Year:

Sum({< Year = { $(=vPrevyear) } >} Amt)

See the Attachment.

Hope that helps and cleared some of your doubts.

Regards

Aviral Nag

Re: How to separate the years in a Straight Table

umartareen — Tue, 22 Jul 2014 09:27:28 GMT

It works perfect. Thank you very much Aviral Nag.

Re: How to separate the years in a Straight Table

aveeeeeee7en — Tue, 29 Jul 2014 11:56:49 GMT

Close this thread by marking this as Correct Answer so that it would be helpful for others.