Data Island table field value not resolving in the charts and straight tables

[JM]

Hi,

We have our data model with [Scale Options] table in data islands, used for formatting. We observed that if we use simple expression in a table everything works fine but when we add set analysis in the same expression, values don't come up properly.

My Assumption is all the fields from data island are not affected by selection made on the data model fields, but that is not our observation when we use these fields in charts.

In the QVW file attached, we have created a straight table. It shows the sales by customer for a year(independent of month). Columns1 & Column2 have same expression to calculate the sales but we have divided the Column2 expression with Scale Factor, and it doesn't bring any sales value like Column 1 (for customers who do not satisfy the current selections), when we select a month. Check the Column3, which shows Sales Factor value.

To reproduce the issue, select a month.

We appreciate some suggestions and guidance to find the causes of the problem and solution to fix it.

Thanks,

J.

[Anil_Babu_Samineni]

I am not triggering the issue. Would you tell us the values when we are not selecting any thing from Month. It is showing 1 for default because When i use filter pane for that Factor values it's associated with only 1.

[JM]

May be I am not good at explaining things . lets start it over.

We wants to control the presentation of numbers they see in the table, i.e. if we want to see all numbers in Thousands they should select 'K' from Scale Options, if we want to seen numbers in Millions, should select 'M' and otherwise 1 as default Scale means no conversion.

We have three expression in that straight table:

First Expression: Sales

Its shows the sales for the whole year(regardless of month selected), its the reference column, which shows actual out put.

Second Expression: Sales (1) or Sales (K) or Sales (M)

It should show the same value as you will see in First expression but divided by the Scale Factor based on Scale Option selected.

Third Expression: Scale Factor

Its just show the Scale Factor value.

Because Scale Factor is a field from data island table, it should be repeated across all rows and shouldn't be affected by selection we make in the filters. It works alright for all filters except the Fiscal Month.

Why it's happening? We don't know but a logical guess would be because Fiscal Month selection is excluded in the expressions by applying set analysis, therefore sales value is being calculated for all the customers who have purchased products throughout the year but it somehow affects the Scale Factor values for those customers who do not have any sales values for the selected month.

I hope it helps you understand the problem. In actual application we will only show the Second expression the rest of the expression will be disabled/deleted.

Cheers,

J.

[ahaahaaha]

Hi,

You have correctly determined the reason. Look at the attached file. I removed Set Analusis expression in SUM() and your problem disappeared. Your sum with Set Analysis expression displays all rows of the table, including the NULL values.

Regards,

Andrey

[JM]

Thanks Andrey for the reply but that is not what I am looking for, if that was the case I wouldn't have asked this question.

I need Set Analysis along with option to control Scale Options.

Is it a product bug, that selection is affecting data island field? and how to fix it?

Thanks,

J.

[JM · Accepted solution]

Hi Guys,

We have our workaround/solution for this problem. Instead of using data Island field directly in the expressions, we have used a precalculated variable in expression.

Let vScaleFactor = '=_ScaleFactor';

and replaced in expression:

Sum({<Fiscal.Month=>} Sales)  / $(vScaleFactor)

The Small Equal Sign has it's performance drawbacks. So use it carefully.

Cheers,

J.