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: 
nicholas5141
Partner - Creator
Partner - Creator

Previous Accumulate Value - Current month Accumulate Value as new field

Hi, Anyone can help out deduct based on accumulate value minus previous month accumulate value as new field as screenshot below

Expected result also as per screenshot.

Hopefully it can done in edit script part.

Thank you.

1 Solution

Accepted Solutions
Kushal_Chawda

Here is script solution

Sales:
LOAD ID,
AccumSales,
Date
FROM
QlikCommunityFile.xlsx
(
ooxml, embedded labels, table is Sheet1);

New:
NoConcatenate
LOAD *,
if(rowno()=1 or ID<>Previous(ID),Peek(AccumSales,(RowNo()-1)-1)-AccumSales,Null()) as MonthlySales
Resident Sales
Order by ID desc ;

DROP Table Sales;

View solution in original post

7 Replies
tresesco
MVP
MVP

May be like attached qvw.

sunny_talwar

If the sorting of ID doesn't matter, you can try this expression:

=Above(TOTAL AccumSales) - AccumSales

Capture.PNG

sunny_talwar

Alternatively, if you have QV12, you can try this:

=Aggr(Above(TOTAL AccumSales) - AccumSales, (ID, (NUMERIC, desc)), Date)

Capture.PNG

Kushal_Chawda

Here is script solution

Sales:
LOAD ID,
AccumSales,
Date
FROM
QlikCommunityFile.xlsx
(
ooxml, embedded labels, table is Sheet1);

New:
NoConcatenate
LOAD *,
if(rowno()=1 or ID<>Previous(ID),Peek(AccumSales,(RowNo()-1)-1)-AccumSales,Null()) as MonthlySales
Resident Sales
Order by ID desc ;

DROP Table Sales;

tresesco
MVP
MVP

Nice colors ! Celebrating Holi in India ?

Kushal_Chawda

Yeah. It's holi. Festival of colors.

nicholas5141
Partner - Creator
Partner - Creator
Author

Thanks!