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: 
Not applicable

Adding rows

See attachment.  I want to take row 1 (Sales) and add to row 2 (Cost of Goods Sold) and get a new row 3 (Gross Profit)  How can this be accomplised.

THANKS!!!

Lance

1 Solution

Accepted Solutions
m_woolf
Master II
Master II

Maybe in load script:

     Sales + [Cost of Goods Sold] as [Gross Profit],

View solution in original post

6 Replies
m_woolf
Master II
Master II

Maybe in load script:

     Sales + [Cost of Goods Sold] as [Gross Profit],

Not applicable
Author

Not sure that would work, the sale number and COGS number is made up of many rows.  For example, each row contains a field(Title) that assigns it a value of Sales or COGS, I only need the diffenrence between the 2 in the Pivot Table Chart.

whiteline
Master II
Master II

LOAD * INLINE [

FRXTitle, NewFRXTitle

Sales, Sales

Cost of Goods..., Cost of Goods...

Indirect COGS, Indirect COGS

SG&A, SG&A

Sales, Gross Profit

Cost of Goods...,Gross Profit

];

and use NewFRXTitle as a pivot dimension instead of FRXTitle.

In this case you will have sum of the first two rows as Gross Profit row.

Not applicable
Author

AMAZING!!!! TRULY AMAZING!!!   Thanks for your help, I don't even understand what you did, I need to learn more about LOAD * Inline, but it worked perfectly!!!!

Not applicable
Author

Can I ask one more favor.

Attached is what I have so far, I am getting the % of Sales column with this,

Sum(AccountBalance)/Sum({<NewFRXTitle={Sales}>}total <[CalendarYear]> AccountBalance)

and I am getting the previous year with this

=Sum({$<CalendarYear={$(=Max(CalendarYear)-1)}>}AccountBalance)*-1

Everything works great with that.  But I am trying to get the %of Sales for the Previous Year, and it does not work.

Sum qlik2.PNG({$<CalendarYear={$(=Max(CalendarYear)-1)}>} {<NewFRXTitle={Sales}>}TOTAL AccountBalance)

  What am I doing wrong?

whiteline
Master II
Master II

What am I doing wrong?

You've used one solution and marked as right another solution.

As for expression, try correct the syntax of set analysis:

Sum ({$<CalendarYear={$(=Max(CalendarYear)-1)}, NewFRXTitle={Sales}>}TOTAL AccountBalance)

or you misstyped the expression ?