topic Re: Straight table blank fields - using set analysis in QlikView

Straight table blank fields - using set analysis

Fri, 31 Aug 2012 13:01:15 GMT

HI all

I am having issues with a table. I cant make data show in Avg B or Avg C columns. The data only appears in Avg B or Avg A when i select an individual month using a 'Month' List box.

I can't understand why i have to select a month for the data to appear.

Month	Avg A	Avg B	Avg C
Dec11	652	889	827
Jul12	598	-	-
Jun12	605	-	-
Aug12	685	-	-

Avg A formula = Avg(Rank_1_Premium)

Avg B formula = avg(aggr(Max({$<[Insurer]={'eCar'},[Premium]={'<99999'}>} [Premium]) ,RiskNo))

Avg C formula = avg(aggr(Max({$<[Insurer]={'Asda OE'},[Premium]={'<99999'}>} [Premium]) ,RiskNo))

Can you help?

Re: Straight table blank fields - using set analysis

whiteline — Fri, 31 Aug 2012 13:19:34 GMT

Try to use double quotes {"eCar"} instead of {'eCar'} in your set epressions.

Re: Straight table blank fields - using set analysis

Fri, 31 Aug 2012 13:21:26 GMT

thanks for the reply. But that Hasn't Made a Difference

Re: Straight table blank fields - using set analysis

Gabriel — Fri, 31 Aug 2012 13:27:34 GMT

Hi,

Lets have a sample data in excel file, please

Regards,

Gab

Re: Straight table blank fields - using set analysis

Fri, 31 Aug 2012 13:28:19 GMT

Sample of the base data?

James Makepeace

MI Analyst - Stats & Actuarial Team

Southern Rock Insurance

Tel: 01454635844

Re: Straight table blank fields - using set analysis

whiteline — Fri, 31 Aug 2012 13:30:04 GMT

Ok, try them step by step.

First without sets

avg(aggr(Max([Premium]) ,RiskNo))

Then

avg(aggr(Max({$<[Insurer]={"eCar"}>} [Premium]) ,RiskNo))

And then

avg(aggr(Max({$<[Insurer]={"eCar"},[Premium]={"<99999"}>} [Premium]) ,RiskNo))

to localize the issue.

Re: Straight table blank fields - using set analysis

Fri, 31 Aug 2012 13:35:31 GMT

All 3 of those steps return blank fields unless I select a certain month

James Makepeace

MI Analyst - Stats & Actuarial Team

Southern Rock Insurance

Tel: 01454635844

Re: Straight table blank fields - using set analysis

whiteline — Fri, 31 Aug 2012 13:41:37 GMT

So your problem is not connected with set analysis.

Try to get the first one work.

Or provide some test data.

Re: Straight table blank fields - using set analysis

Fri, 31 Aug 2012 13:42:56 GMT

Apologies but what is test data??

Would this be the base data set?

Re: Straight table blank fields - using set analysis

Fri, 31 Aug 2012 13:47:24 GMT

i believe the issue to be with the Aggregation of the data. (something im not fantastic at). the reason i think this is when i tinker with the Aggr it populates the fields.

Although when i tinker it takes the one row which was correct, to four rows which are incorrect.

Regards

Re: Straight table blank fields - using set analysis

whiteline — Fri, 31 Aug 2012 13:48:43 GMT

Your data that you use to calculate this expression.

The numers and string values are obsolete you can scrable them.

The table structure is important.

Re: Straight table blank fields - using set analysis

whiteline — Fri, 31 Aug 2012 13:51:05 GMT

Obviusly the problem is in wrong aggregation.

One right result can be explaind easily.

Possible there is only one record of RiskNo in Dec11.

Re: Straight table blank fields - using set analysis

Fri, 31 Aug 2012 14:02:38 GMT

Here is the test data

Re: Straight table blank fields - using set analysis

whiteline — Fri, 31 Aug 2012 14:13:41 GMT

Oh, I've got it.

You discard Month dimension during aggregation.

Use this kind expression:

=avg(aggr(Max([Premium]) ,RiskNo, Month))

Re: Straight table blank fields - using set analysis

Fri, 31 Aug 2012 14:17:28 GMT

Perfect!!

thanks for your help. I knew it would be something as simple as that.

But as they say, it's not simple unless you know how!!

Thanks!!!

Re: Straight table blank fields - using set analysis

whiteline — Fri, 31 Aug 2012 14:26:08 GMT

You are welcome.

Sorry, for so long way.

Just check the right answer to close the question.