# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor III

## Ignore aggr in dimension

Good day!

i have one simple source table, which i load to QV

 Field1 Field2 Value Name1 Value1 1 Name1 Value2 1 Name2 Value1 1 Name2 Value2 1 Name2 Value3 1 Name2 Value4 1 Name2 Value5 1 Name2 Value6 1 Name2 Value7 1 Name3 Value1 1 Name3 Value2 1 Name3 Value3 1 Name3 Value4 1

In QV i created pivot with such order of dimensions: Field1, Field2 and there calculate sum(Value).\

Following result:

 Field1 Field2 sum(Value) Name1 Value1 1 Name1 Value2 1 Name2 Value1 1 Name2 Value2 1 Name2 Value3 1 Name2 Value4 1 Name2 Value5 1 Name2 Value6 1 Name2 Value7 1 Name3 Value1 1 Name3 Value2 1 Name3 Value3 1 Name3 Value4 1

My necessary result - for each Field1 Show all values of Field2 (even if they haven't any value for some Field2 in source table).

 Field1 Field2 sum(Value) Name1 Value1 1 Name1 Value2 1 Name1 Value3 0 Name1 Value4 0 Name1 Value5 0 Name1 Value6 0 Name1 Value7 0 Name2 Value1 1 Name2 Value2 1 Name2 Value3 1 Name2 Value4 1 Name2 Value5 1 Name2 Value6 1 Name2 Value7 1 Name3 Value1 1 Name3 Value2 1 Name3 Value3 1 Name3 Value4 1 Name3 Value5 0 Name3 Value6 0 Name3 Value7 0

This task i must implement without changing load script and changing order of dimensions.

1 Solution

Accepted Solutions
MVP

## Re: Ignore aggr in dimension

If you don't want to change the script.. use as below

Use two Calculated Dimensions

=Aggr(Only({1}Field1),Field1)

=ValueList(\$(=Chr(39) & concat( Field2, Chr(39) & ',' & chr(39)) & Chr(39)))

Expression

Sum(If(Field2=ValueList(\$(=Chr(39) & concat( Field2, Chr(39) & ',' & chr(39)) & Chr(39))), Value ))

Now go to Presentation tab of your Pivot Table and untick suppress zero values

3 Replies
MVP

## Re: Ignore aggr in dimension

```T1:
Field2,
Field1 & Field2 as Key,
Value
FROM
test.xlsx
(ooxml, embedded labels, table is Лист1);

Temp:
Join

Concatenate(T1)
Load * Resident Temp Where Not Exists(Key, Field1&Field2);

Drop Table Temp;
Drop Field Key;
```

Use above script..

Now go to Presentation tab of your Pivot Table and untick suppress zero values

MVP

## Re: Ignore aggr in dimension

If you don't want to change the script.. use as below

Use two Calculated Dimensions

=Aggr(Only({1}Field1),Field1)

=ValueList(\$(=Chr(39) & concat( Field2, Chr(39) & ',' & chr(39)) & Chr(39)))

Expression

Sum(If(Field2=ValueList(\$(=Chr(39) & concat( Field2, Chr(39) & ',' & chr(39)) & Chr(39))), Value ))

Now go to Presentation tab of your Pivot Table and untick suppress zero values

New Contributor III

Thanks)!