8 Replies Latest reply: Jan 26, 2015 4:28 AM by Beaulieu Mathieu

# Sum force to calculate 0 if no values?

Hi,

I am bloked.

I want to do a sum of values in table with an expression.

My problem is that i want to format all results on a same mask (always show all Years even if sum result is 0) .

For exemple the result that i want :

PartsYearsSum result
Part 120153
Part 120140
Part 120132
Part 220152
Part 220143
Part 220130

And the exemple of datas :

PartsDateVal
Part107/01/20131
Part112/08/20131
Part206/06/20141
Part220/09/20141
Part224/11/20141
Part 102/01/20151
Part 104/01/20151
Part 121/01/20151
Part205/01/20151
Part209/01/2015

1

My problem is that for Part1 and Year 2014 i've not entry in the table.

Is it possible to do that in QlikView?

Thanx

• ###### Re: Sum force to calculate 0 if no values?

There are two possibilities:

1) Forcing (concatenating dummy lines to your fact table) to exist records for all partsxyears combinations

2) Using an isolated Calendar (not linked to your fact table) and using an expression with Set Analysis or sum(if(...))

• ###### Re: Sum force to calculate 0 if no values?

Not without generating records in the script for the missing data.

• ###### Re: Sum force to calculate 0 if no values?

Hi,

Thanx for your help.

I Understand, but how can i do that with dynamics datas?

I suppose that we must test if values exists for all years?

If not exist then add line in concatenat statement?

Could you tell me more?

Concatenate(T1)
LOAD * inline [
Parts, Date, Val, Year
Part1, 01/01/2014, 0, 2014
Part2, 01/01/2013, 0, 2013
]
;

• ###### Re: Sum force to calculate 0 if no values?

Temp:
Load *,Year(Date) as Year;
LOAD Parts, Date#(Date,'DD/MM/YYYY') As Date, Val inline [
Parts, Date, Val
Part1, 01/01/2015, 1
Part1, 02/01/2015, 1
Part1, 03/01/2015, 1
Part1, 01/01/2013, 1
Part1, 02/01/2013, 1
];

Join
Load distinct Year Resident Temp;

Final:
NoConcatenate
Load Parts,Year,Alt(Sum(Val),1) As Sum Resident Temp Group by Parts,Year;

Drop Table temp;

• ###### Re: Sum force to calculate 0 if no values?

Use this instead to dynamically create the missing records:

```Concatenate(T1)
LOAD *, Year(Date) as Year;
LOAD
FieldValue('Parts',RecNo()) as Parts,
date(makedate(FieldValue('Year',IterNo())),'DD/MM/YYYY') as Date,
0 as Val
AutoGenerate(FieldValueCount('Parts'))
While IterNo() <= FieldValueCount('Year');
```
• ###### Re: Sum force to calculate 0 if no values?

Hi,

Thanx for your code. With it, i can see that many records are created in my table.

But... In my table with SUM expression, i can't see years where val = 0...

I've tried to check "Show all values" in dimensions properties but it's not working.

I use set analysis in my expression is that the problem?

My expression is :

=Sum({\$<Mouvement={"Livraison"}, DayMonthConcat={"<=\$(VDayMonthToday)"}>}Val)

I've tried to use ALT function but it's not working my expression was :

=Sum(alt({\$<Mouvement={"Livraison"}, DayMonthConcat={"<=\$(VDayMonthToday)"}>}Val,0))

Thanx,

• ###### Re: Sum force to calculate 0 if no values?

Disable the option Suppress Zero-Values on the Presentation tab.

• ###### Re: Sum force to calculate 0 if no values?

Yearhhhh !!!

You are a god !!!

Thanx