topic TOTAL functionality / Data Schema in QlikView

TOTAL functionality / Data Schema

Mon, 08 Nov 2010 22:14:47 GMT

I am having a problem that i have to believe has been solved many times - I suspect that this could be solved by loading the data differently but not sure.

Scenario :- We Sell Products that can have different services activated on them. We are trying to determine the % of units that have each kind of service activated by Product / Geo / Service.

I have attached a QV. - When i select HWGroup = 'HW E75ZZ' and ServiceCategory = 'Subscription' I want the RegUnits Total to be 15 and not 11. (Even though there is not Subscription on the units in 'Americas'

HWGroup	GEO	ServiceCategory	Attach	RegUnits	AttachRate
HW E75ZZ	Americas	Support	1	4	25.00%
HW E75ZZ	Americas	Total	1	4	25.00%
HW E75ZZ	APAC	Subscription	1	6	16.67%
HW E75ZZ	APAC	Support	1	6	16.67%
HW E75ZZ	APAC	Total	1	6	16.67%
HW E75ZZ	EMEA	Subscription	1	5	20.00%
HW E75ZZ	EMEA	Total	1	5	20.00%
HW E75ZZ	Total		3	15	20.00%
Total			3	15	20.00%

Thanks for any help.

Graham

TOTAL functionality / Data Schema

Mon, 08 Nov 2010 22:31:23 GMT

So you are selecting Subscription for Service Category, but for Americas records, you want to ignore Subscription? Change RegUnits to:

COUNT({<Attach.ServiceCategory=>} Distinct Registrations.Serial#)

I doubt that solves your problem though. What determines that Americas should be counted even though it doesn't match the selections?

If you want the other expressions to behave as if Subscription is selected, but not RegUnits, then this may be the solution.

TOTAL functionality / Data Schema

johnw — Mon, 08 Nov 2010 22:31:51 GMT

If I understood the question:

if(Attach,COUNT({<GEO=,Attach.ServiceCategory=>} TOTAL <HWGroup> Distinct Registrations.Serial#))

Which gives you this:

TOTAL functionality / Data Schema

Mon, 08 Nov 2010 22:32:24 GMT

Here is the data file i used.

TOTAL functionality / Data Schema

Mon, 08 Nov 2010 23:22:19 GMT

Thanks Guys, It now works as i want it. - The RegUnits expression in my original QVW should have been.

COUNT(TOTAL <HWGroup,GEO> Distinct Registrations.Serial#)

Once that was corrected The following statement worked perfectly.

IF(Attach,COUNT({<Attach.ServiceCategory=>} TOTAL <HWGroup,GEO> Distinct Registrations.Serial#))

What does this staement do? COUNT({<Attach.ServiceCategory=>}

TOTAL functionality / Data Schema

johnw — Mon, 08 Nov 2010 23:24:48 GMT

That's set analysis syntax for "Ignore the service category that I selected. I want to do the count including all service categories."

TOTAL functionality / Data Schema

Tue, 09 Nov 2010 00:41:53 GMT

Thanks John. One last question.

I actually have 3 levels of Attach.ServiceCategory, ServiceCategory, ServiceSuperGroup and ServiceGroup (all related) . How can i set this up to work at all levels.

Thanks

TOTAL functionality / Data Schema

Tue, 09 Nov 2010 00:51:43 GMT

Not to worry , i figured it out

BookAttach.SC_ProductSuperCategory=,BookAttach.SC_ReportSuperGrouping=,BookAttach.SC_ReportGrouping

=>}

But if you could help me to not display the empty rows it would be appreciated.

Thanks

TOTAL functionality / Data Schema

johnw — Tue, 09 Nov 2010 00:54:05 GMT

I'm not sure I understand the question. If you mean you want to ignore selections at ALL levels, and if I have your field names right, it would work like this:

IF(Attach,COUNT({<Attach.ServiceCategory=,Attach.ServiceGroup=,Attach.ServiceSuperGroup=>} TOTAL <HWGroup,GEO> Distinct Registrations.Serial#))

Edit: Heh, you posted while I was replying. I'm not sure why you're getting empty rows. That's what the if(Attach,...) was for. It would only calculate a value if Attach was there. So when Attach is 0, the expression would return null, and should therefore be suppressed. Make sure that on the Presentation tab, you have a checkmark next to "Suppress Zero-Values", though you do in the example file you posted. If your real file has more expressions than just the three in the example, you might try putting an if(Attach,...) around the other expressions as well. Basically, if ANY expression is non-zero and non-null, the row will show up. So you'd need to force all the expressions to return 0 or null.