Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Localbar
New Contributor

How to add a calculated column in Pivot table

Dear all,

I have a data set :

data.JPG

Can make a pivot table likes below? I'm no idea how to do Opening & Closing Balance.

 

data1.JPG

Thanks for any help.

 

3 Replies
MindaugasBacius
Valued Contributor III

Re: How to add a calculated column in Pivot table

Could you provide us with the data in excel?

Thank you

Localbar
New Contributor

Re: How to add a calculated column in Pivot table

Thank you first. Please find the attached Excel file.

MindaugasBacius
Valued Contributor III

Re: How to add a calculated column in Pivot table

tmp:
Directory;
LOAD fselldate, 
     freddate, 
     fexpdate, 
     FREDITEM1, 
     foutlet1, 
     fcoupon, 
     fstatus,
     Year(Date#(fselldate, 'YYYYMMDD')) as Year_sell,
     Year(Date#(fexpdate, 'YYYYMMDD')) as Year_exp,
     1 as Issued,
     if(Len(Trim(freddate)) = 0, 0, 1) as Redamption
FROM
[..\Downloads\data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Screenshot_1.jpg

I got a little further but I couldn't manage to understand the login behind Opening and Expire expressions.

Could you collaborate on the algorithm?