topic Re: How to Create Two Custom rows in Table in QlikView

How to Create Two Custom rows in Table

Sat, 02 Mar 2013 02:20:46 GMT

I have simple Excel file with tabular data which i loaded in my qvw. Now i need to add couple of calculation in it. The loaded file look like this

Want to add these calculations

1. Trying to create a 'Total Costs' line = 'VARIABLE LOGISTICS COSTS' + 'FIXED LOGISTICS EXPENSE' + 'Other (VLC)
2. Then also 'Total % of Net Sales' = 'Total Costs'/ 'NET SALES'
3. Also trying to create a Column 'Q1A' = 'Jan'13' + 'Feb'13' + 'Mar'13'

The script is simple load from sheet

Re: How to Create Two Custom rows in Table

Sat, 02 Mar 2013 03:04:23 GMT

You are going to find it more flexible if you load that sort of data as a CROSS TABLE. While you can aggregate or total values now, it's making it hard on yourself and less flexible or extensible. A cross table will give you a table with columns for P&L line, Division, Date, and Amount. So then you can aggregate very easily in any way you want. You can use the Table Files... load wizard to load as cross table (last page of this wizard), or code and it'll looke something like this:

Temp:
CrossTable(Date_Tmp, Amount, 2)
LOAD [P&L Line], 
     Division, 
     [41275],    // this will vary depending on source (I just did a quick sample in Excel
     [41306], 
     [41334]
FROM
... (your load source)
Fact:
NoConcatenate LOAD
          *,
          Date(num#(Date_Tmp)) As Date
Resident Temp;
Drop Table Temp;
Drop Field Date_Tmp;

Re: How to Create Two Custom rows in Table

Sat, 02 Mar 2013 03:32:37 GMT

Hi Thank you for your reply.. i am very young in qlikview,, not even month

old,,so really appreciate if you get me going. here is excel and simple

qvw,, please just help me adding one two more rows as explain in my

question .

Regards-

Faisal Mehmood

Colgate Palmolive - GD BW/BI Core Team

+1 973 630 1489

Re: How to Create Two Custom rows in Table

Gysbert_Wassenaar — Sat, 02 Mar 2013 12:55:47 GMT

You can create the subtotals in a pivot chart using Show Partial Sums. If you create a field for the quarters you can also show the subtotals per quarter . See attached example.

Re: How to Create Two Custom rows in Table

Sun, 03 Mar 2013 16:14:16 GMT

Thank You for your reply. I am also looking for additional element in P&L Line

1. Trying to create a 'Total Costs' line = 'VARIABLE LOGISTICS COSTS' + 'FIXED LOGISTICS EXPENSE' + 'Other (VLC)

2. Then also 'Total % of Net Sales' = 'Total Costs'/ 'NET SALES'

Re: How to Create Two Custom rows in Table

Gysbert_Wassenaar — Sun, 03 Mar 2013 17:25:40 GMT

See here

Re: How to Create Two Custom rows in Table

Mon, 04 Mar 2013 13:14:41 GMT

HI Sir , i saw this exanmple , but it does not tell me how to add three values from one column and make it another row ..

P&L Line is column heading with list of values and i want to add these three out of those and make it another row.

1. Trying to create a 'Total Costs' line = 'VARIABLE LOGISTICS COSTS' + 'FIXED LOGISTICS EXPENSE' + 'Other (VLC)

Re: How to Create Two Custom rows in Table

Gysbert_Wassenaar — Mon, 04 Mar 2013 13:42:10 GMT

It does tell you how to do that. But I guess you don't like to do it that way. However the way you probably want to do it is simply not possible. Qlikview is not a spreadsheet. Use a real spreadsheet application to define the layout and which rows should be aggregated.