topic Re: Grouping data in straight table in QlikView

Grouping data in straight table

Thu, 01 Oct 2015 08:06:50 GMT

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.

Re: Grouping data in straight table

pamaxeed — Thu, 01 Oct 2015 09:33:41 GMT

Hi,

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

Thanks,

Patric

Re: Grouping data in straight table

tresB — Thu, 01 Oct 2015 09:38:14 GMT

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

Re: Grouping data in straight table

rubenmarin — Thu, 01 Oct 2015 09:39:08 GMT

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.

Re: Grouping data in straight table

petter — Thu, 01 Oct 2015 09:55:42 GMT

I think this should work for you:

Count( DISTINCT TOTAL <News> RecordID )

Re: Grouping data in straight table

Thu, 01 Oct 2015 11:04:42 GMT

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

Re: Grouping data in straight table

rubenmarin — Thu, 01 Oct 2015 11:56:01 GMT

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

Re: Grouping data in straight table

petter — Thu, 01 Oct 2015 12:07:10 GMT

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.

Re: Grouping data in straight table

tresB — Thu, 01 Oct 2015 12:08:03 GMT

May be simply like:

Dim: News

Exp: Count(News)

Re: Grouping data in straight table

Thu, 01 Oct 2015 13:06:36 GMT

This is what I was looking for.

Thanks a lot!