Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

1 Solution

Accepted Solutions

Re: Sum force to calculate 0 if no values?

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


talk is cheap, supply exceeds demand
8 Replies
Employee
Employee

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.


talk is cheap, supply exceeds demand
Not applicable

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

anbu1984
Honored Contributor III

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


talk is cheap, supply exceeds demand
Not applicable

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Sum force to calculate 0 if no values?

Yearhhhh !!!

You are a god !!!

Thanx

Community Browser