Fixed Static Parameter

Len_Stockdale
4 mths ago
8replies
Answered

I'm new to Pyramid. How do I create in a report or KPI that has a fixed static parameter please.

I want a report or KPI that counts a distinct value when a range is between 1 and 1000. I don't want the user to be able to change the 1 to 1000 range.

Thanks

Len

- Senior Director of Product
- Ian_Macdonald
- 4 mths ago
- Reported - view
Hi ,

Can you explain a little more clearly the Measures, Dimensions involved? What are you Distinct Counting? What is being used for the Range 1 to 1000? In what context do you want to use this?

Thanks,

Ian
- Len_Stockdale
- 4 mths ago
- Reported - view
Hi

Sorry, hopefully this will help.

I am trying to use to Pyramid to replace our current dashboard that exposes the SQL stats of our Data warehouse.

I want to do a distinct count of data warehouse sessions where the result of the SQL queries return rows between x and y. I want to have this as a simple KPI. The row limits are 1 - 1000, 1001-10,000, 10,000-1000,000 and 100,000 +.

So I want 4 KPI's that count DW sessions but each KPI only counts sessions where the result rows are in one of those 4 ranges.

Thanks

Len
- Software Engineer
  
  Raja_Sambasivam
  
  4 mths ago
  
  Reported - view
  Hi I hope this might help ..... I am trying to achieve this using python script in the model .
  
  Choose Python from the script options: Add the following script to the Python node.
  
  import pandas as pd # Sample DataFrame creation df = pd.DataFrame({ 'CustomerName': inputDF["CustomerName"] }) # Define bins and labels based on row index bins = [-float('inf'), 10, 100, 1000, float('inf')] labels = ['0-10', '10-100', '100-1000', '1000+'] # Create a new column with the bin labels based on index df['Row_Index'] = pd.cut(df.index, bins=bins, labels=labels, right=False) # Display the DataFrame print(df)
  
  Note: Replace
  
  CustomerName with data warehouse session column in your actual data.Also you need to replace the Row Ranges in script.
  
  Click the "Auto-detect" option.
  
  After detection, check the preview.
  
  Run the model to process the data.
  
  Now ..
  
  Go to the "Discover" tab.
  Add the Row_Index column to the rows.
  Add the distinct count of the data warehouse session column.
  
  For You Scenario you add Row range as the Filter like the following Image:
  
  Hope I have understood your scenario ...
  
  Thanks ,
  
  Raja Sambasivam
- Len_Stockdale
  
  4 mths ago
  
  Reported - view
  Thanks for the response.
  
  Sadly Python is not an option as this is a direct query and I cannot add nodes into the model.
  
  Len
- Senior Director of Product
- Ian_Macdonald
- 4 mths ago
- Reported - view
Hi

Create a new Custom Column in Formulate. Create a CASE statement that inspects the value of No. Rows in your situation, Sales*5 (to get a suitable range of values) in my scenario, and assigns that value to the appropriate "Bin".

Put your new dimension Binned Value on the rows and your Row Count measure on columns.

Job Done.

Hope that helps!

Ian
- Senior Director of Product
- Ian_Macdonald
- 4 mths ago
- Reported - view
As a KPI:

Cheers!
- Senior Director of Product
- Ian_Macdonald
- 4 mths ago
- Reported - view
Here's the Custom Column script to create the Binned Values attribute:

CASE([SalesData].[Sales]*5>=1 && [SalesData].[Sales]*5<=1000, "1 - 1,000",[SalesData].[Sales]*5>=1001 && [SalesData].[Sales]*5<=10000, "1,001 - 10,000",[SalesData].[Sales]*5>=10001 && [SalesData].[Sales]*5<=100000, "10,001 - 100,000",[SalesData].[Sales]*5>=100001,"100,001+","N/A")

Ian
- Len_Stockdale
  
  4 mths ago
  
  Reported - view
  Thanks. Now setting this up and testing