topic sum into a table (without depending dimension) in QlikView

sum into a table (without depending dimension)

Anonymous — Mon, 23 Jun 2014 14:59:30 GMT

Hi Everybody.

I don't find a solution to my problem. But i know that you're present with a solution.

And so I say you : thanks 😉

My problem is the next one :

My data source is :

Vehicle	Date_of_Control	Defect_on	City_of_verification
A	11/02/2014	Motor	NY
A	11/02/2014	Brake	NY
A	11/02/2014	Light	NY
B	11/02/2014		NY
C	13/03/2014	Motor	Boston
C	13/03/2014	Brake	Boston
D	10/09/2013	Wheel	NY
E	01/06/2013		Boston
F	24/05/2014	Brake	Boston
G	26/05/2014		Boston

And the result must be :

City	Defect_on	Qty_defect_Y2013	Qty_defect_Y2014	Qty_vehicle_verified_Y2013	Qty_vehicle_verified_Y2014	Col3/Col5	Col4/Col6
NY	Motor	0	1	1	2	0	0.5
NY	Brake	0	1	1	2	0	0.5
NY	Light	0	1	1	2	0	0.5
NY	Wheel	1	0	1	2	1	0
Boston	Motor	0	1	1	3	0	0.33
Boston	Brake	0	2	1	3	0	0
Boston	Light	0	0	1	3	0	0
Boston	Wheel	0	0	1	3	0	0

Thanks.

Mon, 23 Jun 2014 15:02:18 GMT

So what have you tried so far that hasn't worked for you? Can you share your qvw that shows what you've done so far?

Anonymous — Mon, 23 Jun 2014 15:26:34 GMT

Hi Robert

I don't reach to calculate the "qty of vehicle" regardless the dimension " Defect_on".

I tried "aggr" : no success

I tried to find a function "Nofilter" ( as in Business Object) for exclude the dimension "defect_on" : no success

How must i do for displaying the qty of vehicle regardless a dimension in a table?

Thanks for your help

Anonymous — Mon, 23 Jun 2014 15:55:57 GMT

for Qty_vehicle_verified_Y2013, use the following expression

count(DISTINCT total <City_of_verification> {<Date_of_Control={'>01/01/2013<=31/12/2013'}, Defect_on=>}Vehicle)

for Qty_vehicle_verified_Y2014,

count(DISTINCT total <City_of_verification> {<Date_of_Control={'>01/01/2014<=31/12/2014'}, Defect_on=>}Vehicle)

for col3/col5, use

=Qty_defect_Y2013/Qty_vehicle_verified_Y2013

and so on.

PrashantSangle — Mon, 23 Jun 2014 16:06:35 GMT

Hi,

Check Enclosed File.

It will help you.

Regards