Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
vpanchuda
Contributor III
Contributor III

Adding data from previous column in pivot table

HI Experts

The Category and month are my dimension and Count of Category is the measure below is the table i got after calculation and Pivoting.

 MonthYear     
CategoryTotalsMay-19Jun-19Jul-19Aug-19Jun-20
B14-31--
B241-3--
B31----1
C11-----
D12-----

 

I need the below calculation as in the table 

CategoryTotalsMay-19Jun-19Jul-19Aug-19Jun-20
B14(Show the Pevious column if Null in this case 4)31--
B241 (Subtract with previous column if there is a value in this case 4-1=3)-3--
B31----1
C11-----
D12-----

 

 

and the result table i intend to get should be like below.

 MonthYear     
CategoryTotalsMay-19Jun-19Jul-19Aug-19Jun-20
B1441000
B2433000
B3111110
C1111111
D1222222

 

Can someone help me, please.

6 Replies
brunobertels
Specialist II
Specialist II

Hi 

Not sure but try this using before() and if () statement as mesure 

 

May be somethink like this: 

if(isnull(sum(sales),before(sum(sales)),before(sum(sales))-sum(sales))

bruno

 

brunobertels
Specialist II
Specialist II

 

if(isnull(sum(sales)),before(sum(sales)),before(sum(sales))-sum(sales))

 

vpanchuda
Contributor III
Contributor III
Author

 

Thanks Bruno

All the values shows blank ,NOt working

brunobertels
Specialist II
Specialist II

Hi 

 

Sorry to read that , can you provide a sample of data please 

vpanchuda
Contributor III
Contributor III
Author

HI Bruno

The table is the same as the 1st table in my query, i have just changed the nomenclature for confidentiality.

Regards

Vinayak

brunobertels
Specialist II
Specialist II

Hi 

 

Sorry after trying somes test  and mesures I can't rich your desired output 

 

Bruno