Calculated Dimension Selecting Value from Associated Tables

[campbellr]

Hi All

I'm having an issue with a calculated dimension in a chart that seems to go away if I reduce the amount of data in the file.

The situation:

I have a number of ways to arrive at a categorisation for an invoice. I have a table of invoices with one option within, I then have a bunch of linked tables with other options for a categorisation. As per the code for my calculated dimension I have an order preference for selecting which method to use. I have three such calculated dimensions for showing the source table for the category, the category and the dollar amount associated with it. The first two appear to work a treat but the one to return the dollar value has some off behaviour.

For a large number of rows the dimension shows a null value, like it's failing to find a result. If I comment out any of the rows 2-4 the results appear to come back to being correct. If I load only some of the data from each table the result is correct.

If I replace the field references with a number, the result is correct.

code for dimension:

if(len(TaxonomyCode_AP_GL)>1 AND Categorised_AP Like 'Categorised*SCS',[Spend_AP],

  if(len(TaxonomyCode_SSD_HSID_PO#)>1,[Spend_SSD_HSID_PO#],

  if(len(TaxonomyCode_SSD_Sup_Inv#)>1,[Spend_SSD_Sup_Inv#],

  if(len(TaxonomyCode_SSD_CH_Inv#)>1,[Spend_SSD_CH_Inv#],

  if(len(TaxonomyCode_PO)>1 and Categorised_PO<>'UNSPSC_PO_Cat',[Spend_PO],

  if (len(TaxonomyCode_AP_GL)>1,[Spend_AP], // and Categorised_AP not *SCS implied

  if(len(TaxonomyCode_PO)>1,[Spend_PO],  // and Categorised_PO='UNSPSC_PO_Cat' implied

  0)

  )

  )

  )

  )

  )

)

I tried to put this in as an expression instead of a calculated dimension and it is giving variable results, sometimes, depending on the selections the straight table object shows the message "Total Specifier must be a valid field" I'm having trouble narrowing it down to any specific lines that cause this error.

Any suggestions gratefully received.

Ron

[Digvijay_Singh]

Not sure but what if you use valuelist in calculated dimension to specify literal dimension values and then decide in expression using Pick(Match(Valulist(...),Value1,Value2..),Expression1,Expression2...)) to check conditions and actual expression..really don't know if it fits in your context or not.

[campbellr]

Hi Digvijay

I'll take a look into how pick-match works, one of my colleagues did suggest it then thought it might not work for my situation. The fields referred to in my nested IFs are all in separate associated tables. This bit that confuses me is that the nested IFs appear to work fine for two of the three fields I'm returning. It only seems to fail with the numeric field, even if I change the load script to bring the numeric field in as text.

Ron

[campbellr · Accepted solution]

This was a while ago but I found what I consider to be a neater solution.

Each of the possible options from the IFs were an 8 digit number, I was looking to return the number based on a mapping with priority, ie find a match a by a better method. Each method returned 8 digits if a match was found and an empty string where no match found.

My solution was simply to concatenate all of the results and take the first 8 digits as the result. Thus giving me the first match.