topic Re: Adding rows in QlikView

Adding rows

Tue, 04 Sep 2012 15:02:58 GMT

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

Re: Adding rows

m_woolf — Tue, 04 Sep 2012 15:10:50 GMT

Maybe in load script:

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

Re: Adding rows

Tue, 04 Sep 2012 15:17:22 GMT

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.

Re: Adding rows

whiteline — Wed, 05 Sep 2012 08:53:06 GMT

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.

Re: Adding rows

Wed, 05 Sep 2012 12:48:00 GMT

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!!!!

Re: Adding rows

Wed, 05 Sep 2012 13:44:16 GMT

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 ({$<CalendarYear={$(=Max(CalendarYear)-1)}>} {<NewFRXTitle={Sales}>}TOTAL AccountBalance)

What am I doing wrong?

Re: Adding rows

whiteline — Wed, 05 Sep 2012 14:54:34 GMT

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 ?