Display Text Value Instead of Numeric Value
I have three columns one language column, gender column and count column (numeric). Inside my language column I have different languages as text , inside my gender column I have more than three classification of genders like male , female ,others ,unknown etc ..whereas in my count column I have count of the the genders as numeric value.
Now I want to find the Max and min of the column count but I want to display the text of my gender column like Male , Female instead of my numeric value .
Please refer the attached images .
How can we achieve this ?
5 replies
-
Hi ,
Pyramid operates on what are known as dimensional models. As such, it takes each field n the data table, extracts the list of unique values from it as a dimension, say a list of languages or genders. It treats these independently, so that when you place two dimensions on your grid, they are nested and each element in one dimension is related to every element in the other dimension though related facts. Thus you cannot, by default, create a report where the dimensions are "Side by side" rather than nested.
So we're going to have to bend Pyramid bit to get what we want. I need to effectively get my max / min gender, language etc and assign it to a common dimension as members, so I can list them as you require.
First step is to create "holding" members that will host the calculation. Choose a dimension with few real members and create calculated members though formulate to match the dimensions you want to display. In my case here, Gender, Country and Manufacturers. We can initialise them to null as a start.
Duplicate this member 3 times and save them named for your other dimensions, in my case country and manufacturer.
For each of the dimensions concerned we need to make a sorted list of the members. Create a new dynamic list in formulate and drag the standard list block onto the canvas. Populate it with all the elements from that dimension.
Apply a Sort by Value block, choose the measure to sort by, in my case Transaction Count. As this is being hosted in the Channel, I don't want the data filtered by Channel, so choose the ALL option or select all the elements.
Repeat for your other dimensions. Here's the same thing in PQL Script:
Order({AllMembers([Customer Profile].[Gender])},(AllMembers([Channel].[Channel]),[measures].[Data Transaction Count]),desc)We now need to create our MAX and MIN calculations. In the MAX column, we need to identify the Row and then take the first element of the sorted list for that row / dimension and get is caption. We need to do this for each row, so the best approach is to use a CASE statement.
We can read this as:
"If the row header is Gender, get the first element of the sorted list of genders and set the calculation to its caption."
"If the row header is Country, get the first element of the sorted list of countries and set the calculation to its caption."
"If the row header is Manufacturer, get the first element of the sorted list of manufacturers and set the calculation to its caption."
Otherwise set to N/A.
We do the same for the MIN calculation, except we use LASTELEMENT instead:
Here's the same in PQL script:
CASE( [Channel].[Channel] IS [Channel].[Channel].+[6f3bc771-f2d2-46f8-9e2c-cb557eb75deb], FIRSTELEMENT({[Customer Profile].[Gender].*[f1899463-dd94-4b4e-89a9-37e85a74c6e4]}).CAPTION, [Channel].[Channel] IS [Channel].[Channel].+[efa5046c-291e-4787-a1f6-c04d65906cf2], FIRSTELEMENT({[Customers].[Country].*[9567863d-b4a6-4c91-a34f-b67b2d6610a7]}).CAPTION, [Channel].[Channel] IS [Channel].[Channel].+[58e78fe3-f62d-40b3-a3f1-37e15ebea24e], FIRSTELEMENT({[Manufacturers].[Manufacturer].*[15f4b7d4-b530-454e-a892-3432a1c4e387]}).CAPTION, "N/A" )Finally, we can place Channel on Rows of my grid, selecting the custom member we created, and MIN and MAX Measures on columns. Filtering by Product subcategory we can see the grid change:
Hope that helps.
Ian
-
Hi
The simple answer is, no.
As explained above, you are fighting the core semantic model approach of Pyramid. While we can bend it so far, we can't break it. In order to achieve your desired output as dynamic and flexible analysis, you will have to reshape your data so that you have a column that contains all the names of data fields (gender, language and so on), a column that contains the different subfield values (German, English, French for language, Male, Female etc. for Gender and so on) and a column with their respective counts. You can then build a model sung this data and have the flexibility for the analysis you require.
BTW, doing it in SQL is no easier :-).
Hope that helps.
Ian
-
Hi
I Understood, Now I have the Dataset as the attached picture that is field, Subfield and Value Column. I want to get the Minimum and Maximum Value like my table-2
I need to achieve this via PQL like the following SQL query:
"SELECT Field, subfield, MAX(Value), MIN(Value)
FROM table
GROUP BY Field;"If needed anymore information from my side, I can Provide that too.
-
Great. I've also reshaped your data so that for my example when I create a Discovery query with Field, Subfield and Count I get
The calculation is essentially the same as previously described, except that now my data is shaped better I don't need to create the dummy members, just apply the calculation directly to the subfield for each field.
Use the Order() function to order descending a standard list containing all the subfield values, using a datapoint to specify the Measure to be used and the Field currentmember. the first element will be the max, so FirsElement will retrieve that member and the .caption on the end will return it's name.
Duplicate the measure, change FirstElement to LastElement and rename to Min.
Then put Field on the Rows and Max and Min on the columns and you're good to go:
Hope that helps.
Ian
-