topic Re: How to add a calculated column in Pivot table in QlikView

How to add a calculated column in Pivot table

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

Dear all,

I have a data set :

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

Thanks for any help.

Re: How to add a calculated column in Pivot table

MindaugasBacius — Thu, 17 Jan 2019 08:01:54 GMT

Could you provide us with the data in excel?

Thank you

Re: How to add a calculated column in Pivot table

Localbar — Thu, 17 Jan 2019 08:30:46 GMT

Thank you first. Please find the attached Excel file.

Re: How to add a calculated column in Pivot table

MindaugasBacius — Thu, 17 Jan 2019 08:59:03 GMT

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

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?