Is there a QlikView feature that is similar to the Calculated Item feature in Excel Pivot Tables?

[#1]

Hello,

This is my first post in this community. I've been an end user of QlikView dashboards for a couple of years, but I've recently found myself on the design and development side of things.

I'm trying to create a calculated option under a column based on the existing options in that column. I am not trying to create a new column.

The calculated option I'm trying to create is bolded below. The data set I'm working with contains the first three options for each record (about 300) in my data set.

Example:

Forecast Type
Budget
Forecast
Actuals
Remaining Budget (Budget - Actuals)

I'd really appreciate any thoughts on the best approach here.

Thanks!

Chris

[Anil_Babu_Samineni]

Do you mean This??

Interval(Budget - Actuals)

[marcus_sommer]

To get this result you could simply use something like this: sum(Budget) - sum(Actuals). If this shouldn't only an expression-column or a value within the total column/row - they could be separated calculated with: if(dimensionality() = X, expr1, expr2) - else a separate dimension-value then you need to add this dimension-value. Either with valuelist() in a calculated dimension or (mostly) better as an additionally value within the script like:

concatenate (YourTable)

Load 'Remaining Budget' as [Forecast Type] autogenerate 1;

and then you could react on this with a small if-loop within the expression like:

if([Forecast Type] = 'Remaining Budget', sum(Budget) - sum(Actuals), AnotherExpression)

- Marcus

[#4]

Thanks for your replies.

I'm completely new to scripting in QlikView, and I appreciate your patience.

Any guidance on where this code should go in the script below?

LOAD Year,

     Month,

     [SunTrust ID],

     [Project Name],

     Phase,

     Status,

     A,

     Portfolio,

     SubPortfolio,

     [Decision Category],

     [Forecast Type],

     Supplier,

     [Expense Type],

     Value

FROM

(ooxml, embedded labels, table is Sheet1);

[marcus_sommer]

Try the following:

YourTable:

LOAD Year,

     Month,

     [SunTrust ID],

     [Project Name],

     Phase,

     Status,

     A,

     Portfolio,

     SubPortfolio,

     [Decision Category],

     [Forecast Type],

     [Forecast Type] as [Forecast Type Chart Only],

     Supplier,

     [Expense Type],

     Value

FROM

(ooxml, embedded labels, table is Sheet1);

concatenate (YourTable)

Load 'Remaining Budget' as [Forecast Type Chart Only] autogenerate 1;

If you need your field [Forecast Type] within a listbox and other charts, too it might be useful to create a complete new field only for this purpose.

- Marcus

[#6]

Thanks, Marcus,

I was able to create the field under "Forecast Type", but I can't get the calculation to work.

Also, I don't know what the purpose of the "Concatenate" script is, and I couldn't get it to work. I tried the text you gave me, and I also tried putting the location of my source file in the Concatenate script, but that returned an error.

Here's the updated script:

LOAD Year,

     Month,

     [SunTrust ID],

     [Project Name],

     Phase,

     Status,

     A,

     Portfolio,

     SubPortfolio,

     [Decision Category],

     [Forecast Type] as [Forecast Type Chart Only],

     Supplier,

     [Expense Type],

     Value

FROM

(ooxml, embedded labels, table is Sheet1);

Load 'Remaining Budget' as [Forecast Type Chart Only] AutoGenerate 1;

[marcus_sommer]

Concatenate will add the table from the second load to the first table - otherwise you creates two tables (which in this case also should work). Therefore try it will the exact example and use also table-names for your tables then without qlik will use some generic table-names which make it difficult to keep an overview and to control table-operations like concatenating, joining or mapping.

- Marcus