Missing Dimension values

[#1]

Hi Experts,

I have data like below:

ID	Grade	Score
1	1	273
2	1	275
3	1	279
4	1	311
5	1	325
6	1	330
7	1	332
8	1	333
9	1	345
10	1	346
11	1	347
12	1	348
13	1	349

Grade	Low	High
1	250	360
2	265	400

As the qvf file attached, I can build a bar chart,

However, there are two things I want to have here:

1. I don't want to miss any score range even it is 0 count, such as 280-300

2. I need the all the score ranges from Low to High(Data should read from LowHigh table at run time) with interval 20 in my bar chart:

Score Range	IDCount
250-269	0
270-289	3
290-309	0
310-329	2
330-349	8
350-360	0

How can I achieve these?

Thanks for you help in advance!

Jing

[Gysbert_Wassenaar]

The class function doesn't create the 'missing' range if there's no data. You don't have score values in that range so the range cannot be created. If you want that range despite that then you need to add dummy data so at least one score value in that range exists in your data.

[swuehl]

You can try creating the complete range of buckets in your script from your LowHigh table as a dimensional table, then map your Scores to the buckets using ApplyMap().

The mapping could be done like described here:

creating an age group from a date field

I can't easily modify & reload your script, so if you need any further help, it would be good if you could post the excel source data.

Stefan

[rbecher]

You could use ValueLoop() although this is not very dynamic:

Dimension:

ValueLoop(260, 340, 20)

Measure:

Sum(If(Score >= ValueLoop(260, 340, 20) AND Score < ValueLoop(260, 340, 20) +20, 1, 0))

[#5]

thanks Swuehl! I have attached my source data. Please use sheet1 and sheet2

[#6]

Thanks. I tried and I need dynamically show the results

[rbecher]

You could use variables to set min/max dynamically..

[swuehl]

Have a look at attached sample.

Your buckets seem to be depending on grade, so I assume you are using the graph with a single grade selected?

[#9]

Thank you so much swuehl! This almost solve my puzzle though the actual data is more complicated. I will have at least one more selection for my bucket, not only grade. I added it in attached new qvf. Could you please check if the data load script is correct? I guess I need to learn more about ApplyMap.

One more thing I want is that the last bucket should just go up to High not exceed the High, ex. 385-400, not 385-404 to follow the 20 interval. How can I limit it?

I also attached my new data sheet.

Thanks,

Jing

[swuehl · Accepted solution]

I've added Content to the key for mapping. I've also limited the upper bound for the buckets.

LowHigh:
LOAD [Content],[Grade], BucketLow, BucketHigh, BucketLow & ' - ' & BucketHigh as Bucket;
LOAD [Content],
  [Grade],
   [Low]+(iterno()-1)*20 as BucketLow,
      RangeMin([Low]+(iterno())*20-1,High) as BucketHigh,
   [Low],
   [High]
FROM [lib://ACCRBottomAnalysis/sample1.xlsx]
(ooxml, embedded labels, table is Sheet2)
WHILE [Low]+(iterno()-1)*20 <= [High];

MAP:
MAPPING LOAD 
    [Content]&'-'&Grade&'-'& (BucketLow+iterno()-1), Bucket
Resident LowHigh
WHILE BucketLow+iterno()-1 <= BucketHigh;

Score:
LOAD [ID],
  [Content],
  [Grade],
  [Score],
    ApplyMap('MAP', [Content]&'-'&[Grade]&'-'&Score) as Bucket
FROM [lib://ACCRBottomAnalysis/sample1.xlsx]
(ooxml, embedded labels, table is Sheet1);