4

Fixed Static Parameter

Hi

 

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

8 replies

null
    • 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

Content aside

  • Status Answered
  • 4 Likes
  • 4 mths agoLast active
  • 8Replies
  • 48Views
  • 4 Following