Data different than an aggregation in the crosstab intersection

Joan Poblet shared this question 6 years ago
In Progress

Morning,


Is there any way to have a value different than an aggregation in the intersection between a column and a row within a crosstab? For instance, a label or a date (always a unique value).


Many thanks!


Best Regards,

Joan.

Replies (8)

photo
1

Hi Joan,

Thanks for reaching out. I am a bit confused as to what exactly you are seeking here. Would you mind elaborating with an example screenshot?

Thanks,

Nathan

photo
1

Hi Nathan,


I'm sorry for the delay, please find three examples in the attached excel file. The first one is the only one we were able to create but we would also like to create crosstab like examples 2 and 3.


Many thanks.


Best Regards,

Joan.

photo
1

Hi Joan,


Thank you for the clarification. Unfortunately, as the cross-tab error shows, the underlying SQL requires you to have "one Column dimension, one Row dimension, and one aggregated metric field". In this case there is no way to get around having to aggregate a metric. And while it is possible to make text or dates a metric, aggregating them with count does not suit your needs.


I believe that the best way to accomplish this will be through the use of append sub-queries. Using your 'Booking Level' data as an example:


Create a sub-query for each year, placing level into columns, and filtering by that year. You can then re-name the columns to reflect the filtered year, producing a graph that looks very similar to your desired result.


627c1ab42cb2dd0b248477aeed385b10


Here Is how I have the filters set up:


6f8fb6a718ccb49de5ebacefb4e28923


I apologize for the inconvenience and please let me know if this solution meets your needs.


Regards,

Nathan

photo
1

Hi Nathan,


Many thanks for your help, we will keep your proposal in mind in order to solve that kind of situation.


Best Regards,

Joan.

photo
1

Hi Joan,


I was just wondering if you have had any luck in resolving this issue? Please let me know if you have any additional questions, or if you would be okay with me closing this ticket.


Regards,

Nathan

photo
1

Hi Nathan,


Yes, we did. We were able to get the report with your proposal, you can close the ticket.


Many thanks for your help.


Best Regards,

Joan.

photo
1

Hi Joan,


Glad to hear you got both tickets resolved! I have closed them, but please don't hesitate to reach out if you have any additional questions or concerns!


Regards,

Nathan

photo
1

Hi,

Is this feature now available in 9.7.2 i.e. using values other than numeric to show at the intersection of column and a row within a crosstab. I would like to show dates. I tried using Min/Max of date but it shows up as blank.

Below is a sample.

NOTE: My column values are dynamic and hence I cannot have a subquery for each value.

i.e. The values in Category and Item fields are dynamic.

Leave a Comment
 
Attach a file