Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Rsaiq
Creator
Creator

Creating column from existing column

Hi All,

I am creating a new column from existing column in qliksense

Need to create the new column by summing the value row by row from existing column but there is 1 condition.

Condition is like we need to add value row by row when account=1 and if account=2 comes then select all previous values of account 1 and past for account 2

Please see below screenshot for more understandings

Rsaiq_0-1626099276162.png

I have calculated this through expresion but my expression not working correcting as per my requirement on account=2 condition.

My expression is :-

=If(Account='1',Aggr(
RangeSum(Above(Sum(Amount), 0, RowNo()))
,Amount),above(total Amount))

My Output: -

Rsaiq_3-1626100590632.png

 

If possible then please tell me how can i create this column from script too.

Kindly find attached dataset file .

Thanks

 

Labels (2)
1 Solution

Accepted Solutions
micheledenardi
Specialist II
Specialist II

Demo_Tmp:
LOAD
	RecNo() as Rec,
    Account,
    "Date",
    "Type",
    Amount
FROM Demo.xlsx
(ooxml, embedded labels, table is Dataset);

Demo:
Load *,
    if(Account=1,RangeSum(peek('New Amount') , [Amount]),peek('New Amount')) as [New Amount]
Resident Demo_Tmp 
	Order by Rec;
    
Drop Table Demo_Tmp;

 

2021-07-29 16_55_18-test - Il mio nuovo foglio (17) _ Foglio - Qlik Sense.png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

2 Replies
JustinDallas
Specialist III
Specialist III

This looks like a PandL type of situation.  I would suggest looking at one of the various PandL dashboards out there.  Just a warning, PandLs require significant data model work to capture the type of functionality you are seeking.

micheledenardi
Specialist II
Specialist II

Demo_Tmp:
LOAD
	RecNo() as Rec,
    Account,
    "Date",
    "Type",
    Amount
FROM Demo.xlsx
(ooxml, embedded labels, table is Dataset);

Demo:
Load *,
    if(Account=1,RangeSum(peek('New Amount') , [Amount]),peek('New Amount')) as [New Amount]
Resident Demo_Tmp 
	Order by Rec;
    
Drop Table Demo_Tmp;

 

2021-07-29 16_55_18-test - Il mio nuovo foglio (17) _ Foglio - Qlik Sense.png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.