Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
umartareen
Creator II
Creator II

How to separate the years in a Straight Table

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.

1 Solution

Accepted Solutions
aveeeeeee7en
Specialist III
Specialist III

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

View solution in original post

3 Replies
aveeeeeee7en
Specialist III
Specialist III

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

umartareen
Creator II
Creator II
Author

It works perfect. Thank you very much Aviral Nag.

aveeeeeee7en
Specialist III
Specialist III

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