Grouping data in straight table

[#1]

Hi All,

I have a dynamic straight table with few dimensions and measures.

In my data schema, I have a field called "News" which have few values, like: "Sports", "Economics", etc.

I'd like to add an expression field to my straight table, which will show for each value in "News", how many records exists for this value.

For example, If I filter my data for October 2015, I'll see: "Sports (30 records), Economics (50 records)" with regard to this dimension and other dimensions selected in this table.

I tried to use aggr() function in my straight table, but it doesn't work since I don't know in advance which dimensions will be selected in the straight table (Users can add / remove dimensions)

Thanks.

[pamaxeed]

Hi,

can you provide a sample app, so that it is simpler for us to understand?

Thanks,

Patric

[tresesco]

Resolution would actually depend on how you are defining (expression) dimension to make it dynamic. Try to share your sample qvw.

[rubenmarin]

Hi Yehuda, If the user selects the dimensions from a field you can try this:

Concat(Aggr(News & '(' & Count(News) & 'records)', $(=GetFieldSelections(DimensionSelectorFieldName)), News), ',')

Not tested.

[petter]

I think this should work for you:

Count( DISTINCT TOTAL <News> RecordID )

[#6]

Hi,

An example is attached. The text box is the desired field (result) I want in my table.

Please take into account that there might be more dimensions which can be in the main table and the straight table (not only Date_Id & Month_Id).

Thanks

[rubenmarin · Accepted solution]

Hi, you'll need some dynamic aggr(), check attachment to see a possible solution.

[petter]

You have to adjust the fields mentioned after total within the <> according to the dimensions you include in your chart. So giving a fixed expression for you when you say that "there might be more dimensions" makes it impossible.

Aggr() will maybe not be necssary - and should be avoided if you can- unless you want to aggregate on more dimensions than the ones that are used in your chart. TOTAL is the most efficient solution but is relies on you being able to include only the dimensions from the chart that will give you the right level of total.

[tresesco]

May be simply like:

Dim: News

Exp: Count(News)

[#10]

This is what I was looking for.

Thanks a lot!