topic Expression Total / Sum of Rows in QlikView

Expression Total / Sum of Rows

Wed, 05 Aug 2009 23:18:45 GMT

Hey guys, I'm struggling with writing an expression such that expression total is exactly equal to sum of rows.

I have 3 elements - Item, Commodity, Std Price. I created a straight table chart with Commodity as a dimension. I have 3 expressions -

First one is the Count of Items for each commodity Count(Item)

Second one is the Count of Items with Std Price > 0 sum(If(StdPrice = 0.00,0,1))

Third one has two conditions. Items should have Std Price = 0 and the commodity that the item belongs should have atleast one item with Std Price > 0

If(If(sum(total<Commodity> If(StdPrice = 0,0,1))>0,1,0) > 0,
sum(total<Commodity> If(StdPrice> 0,0,1)),0)

It is working as expected at the individual commodity level. But at the summary level, it is not right. Please help with the formula for the third expression (WithoutStd_WithCommodity) so that the summary on the top is 5 and not 10.

Thanks in advance for your help.

Expression Total / Sum of Rows

Wed, 05 Aug 2009 23:28:42 GMT

I am having trouble with uploading the qlikview file....I'll keep trying but in the mean time here is the script for my problem above.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Prices]:
LOAD * INLINE [
Item,Commodity,StdPrice
'1234' , 'ELECTRICAL',0.00
'3426' , 'PLUMBING',2.23
'7852' , 'PLUMBING',0.00
'7755' , 'PLUMBING',0.00
'6565' , 'PLUMBING',798.52
'2020' , 'ELECTRICAL',0.00
'5252' , 'ELECTRICAL',0.00
'4565' , 'ELECTRICAL',2.22
'3555' , 'ELECTRICAL',0.75
'4625' , 'JANITORIAL',0.00
'5253' , 'JANITORIAL',0.00
'4566' , 'JANITORIAL',0.00
'3557' , 'JANITORIAL',0.00
'4622' , 'JANITORIAL',0.00
];

Expression Total / Sum of Rows

Thu, 06 Aug 2009 01:52:51 GMT

I figured it out guys....I need to embed the whole expression within an aggr and a sum function...

sum(

aggr(

If(If(sum(total<Commodity> If(StdPrice = 0,0,1))>0,1,0) > 0,
sum(total<Commodity> If(StdPrice> 0,0,1)),0)

,Commodity)

)