Sum force to calculate 0 if no values?

[#1]

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 :

Parts	Years	Sum result
Part 1	2015	3
Part 1	2014	0
Part 1	2013	2
Part 2	2015	2
Part 2	2014	3
Part 2	2013	0

And the exemple of datas :

Parts	Date	Val
Part1	07/01/2013	1
Part1	12/08/2013	1
Part2	06/06/2014	1
Part2	20/09/2014	1
Part2	24/11/2014	1
Part 1	02/01/2015	1
Part 1	04/01/2015	1
Part 1	21/01/2015	1
Part2	05/01/2015	1
Part2	09/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

[Clever_Anjos]

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(...))

[Gysbert_Wassenaar]

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

[#4]

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
];

[anbu1984]

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;

[Gysbert_Wassenaar]

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');

[#7]

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,

[Gysbert_Wassenaar · Accepted solution]

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

[#9]

Yearhhhh !!!

You are a god !!!

Thanx