Hi Shir Ben-Gal

By creating new table we can achive the resuls

• select the new model for Discover, locate the "Add New Database" icon on the right.

  

   

• Click on the icon and select the Custom Query option.

• 

   

•  

• Use the following script to create a distinct day count based on the customer:

  Sample  Copy code

  SELECT 
    [Customer Name], 
    COUNT(DISTINCT[Purchase History]) AS count_recommendation_date
FROM 
   [data_synthetic]
GROUP BY 
    [Customer Name];

• After updating the script, check the selection box to show the measure as a dimension.

• 

• Click the Finish button to apply the changes.

• In the Dimension Panel, select the count_recommendation_date and then create a customer count via customerName.

• 

• This will display the desired scenario where you can see the customer count grouped by the number of distinct recommendation days.

Chart

Hope that helps!.

Thanks & Regards
Raja Sambasivam
 

Hi Shir Ben-Gal
Using Custom Visual Script in Pyramid Analytics

To achieve your desired scenario without creating a new table, you can follow these steps using the Custom Visual Script option in Pyramid Analytics:

1. Add Columns to Dropzones:

   • Drag the Account column into the Row Dropzone.
   • Drag the Recommendated date DST Count into the Values Dropzone
   •  

2. Use Custom Visual Script:

   • In the Custom Visual section of Pyramid Analytics, you can utilize the D3.js chart library for visualization. D3.js provides a wide range of charts and visual styles to choose from, allowing you to pick one that suits your needs.
   • Here’s a sample script you can adapt for your visualization using D3.js:
     Place the Code:

     function main() {
         // Load the D3.js script first
         loadScript('https://d3js.org/d3.v4.js', function() {
             // Even listener after the loads
             cvApi2.canvas.addEventListener(cvApi2.enums.Events.Render, render);
         });
     }
     
     
     function loadScript(src, callback) {
         // Check if script is already loaded
         if (document.querySelector(`script[src="${src}"]`)) {
             console.log('Script already loaded:', src);
             if (callback) callback();
             return;
         }
     
         var script = document.createElement('script');
         script.src = src;
         script.onload = function() {
             console.log('Script loaded:', src);
             if (callback) callback();
         };
         script.onerror = function() {
             console.error('Error loading script:', src);
         };
         document.head.appendChild(script);
     }
     
     function render() {
         var currentTrellisedData = cvApi2.resultSet.data.getCurrentTrellisData();
     
         const Rows = cvApi2.utils.getDropzone(cvApi2.enums.DropZoneType.Rows);
         let RowsValues = Rows.chips[0].elements.map(item => item.memberData.caption);
     
         var dataPoints = currentTrellisedData.datapoints;
         let values = dataPoints.map(item => item.numerics.value.rawValue);
         console.log(values);
     
         const groupedCounts = {};
     
         // Iterate through the array to count occurrences
         values.forEach(num => {
             if (groupedCounts[num]) {
                 groupedCounts[num] += 1;
             } else {
                 groupedCounts[num] = 1;
             }
         });
     
         console.log(values);
     
         const resultArray = Object.keys(groupedCounts).map(key => {
             return { number: parseInt(key), count: groupedCounts[key] };
         });
     
         // Output the result
         console.log(resultArray);
     
         const element = cvApi2.canvas.getHTMLElement();
         const styles = cvApi2.canvas.style.pyramidThemeStyle;
     
         // Ensure the divId is unique
         const divId = "chartArea" + currentTrellisedData.column.toString() + "-" + currentTrellisedData.row.toString();
         const foreignObjectId = 'foreign-' + divId;
     
         let div = element.querySelector("#" + CSS.escape(divId));
     
         if (div === null) {
             // Create div element inside SVG for D3
             let foreignObject = document.createElementNS('http://www.w3.org/2000/svg', "foreignObject");
             foreignObject.setAttribute('id', foreignObjectId);
             foreignObject.setAttribute('height', cvApi2.canvas.height);
             foreignObject.setAttribute('width', cvApi2.canvas.width);
     
             div = document.createElement("div");
             div.style.backgroundColor = cvApi2.canvas.backgroundColor;
             div.id = divId;
     
             // Add div element to existing element
             element.appendChild(foreignObject).appendChild(div);
         } else {
             let foreignObject = document.getElementById(foreignObjectId);
             foreignObject.setAttribute('height', cvApi2.canvas.height);
             foreignObject.setAttribute('width', cvApi2.canvas.width);
             div.style.backgroundColor = cvApi2.canvas.backgroundColor;
         }
     
         div.style.width = cvApi2.canvas.width + 'px';
         div.style.height = cvApi2.canvas.height + 'px';
     
         // Clear the div before rendering the new chart
         d3.select(div).selectAll("*").remove();
     
         // D3 Bar Chart Setup
     
           var margin = { top: 10, right: 50, bottom: 10, left: 30 },
             width = cvApi2.canvas.width - margin.left - margin.right,
             height = cvApi2.canvas.height - margin.top - margin.bottom;
     
         // Append the SVG object to the div
         var svg = d3.select(div)
             .append("svg")
                 .attr("width", width + margin.left + margin.right)
                 .attr("height", height + margin.top + margin.bottom)
             .append("g")
                 .attr("transform", "translate(" + margin.left + "," + margin.top + ")");
     
         // Add X axis
         var x = d3.scaleLinear()
             .domain([0, d3.max(resultArray, function(d) { return d.count; })]) // Dynamic max count
             .range([0, width]);
         svg.append("g")
             .attr("transform", "translate(0," + height + ")")
             .call(d3.axisBottom(x))
             .selectAll("text")
                 .attr("transform", "translate(-10,0)rotate(-45)")
                 .style("text-anchor", "end");
     
         // Y axis
         var y = d3.scaleBand()
             .range([0, height])
             .domain(resultArray.map(function(d) { return d.number; })) // Using number from resultArray
             .padding(0.1);
         svg.append("g")
             .call(d3.axisLeft(y));
     
         // Bars
         svg.selectAll("myRect")
             .data(resultArray)
             .enter()
             .append("rect")
             .attr("x", x(0))
             .attr("y", function(d) { return y(d.number); })
             .attr("width", function(d) { return x(d.count); })
             .attr("height", y.bandwidth())
             .attr("fill", "#69b3a2");
     
         // Add data labels
         svg.selectAll("myLabels")
             .data(resultArray)
             .enter()
             .append("text")
             .attr("x", function(d) { return x(d.count) + 5; }) // Positioning labels
             .attr("y", function(d) { return y(d.number) + y.bandwidth() / 2; }) // Centering labels vertically
             .attr("dy", ".35em") // Adjusting vertical alignment
             .text(function(d) { return d.count; }) // Displaying the count
             .attr("fill", "black"); // Label color
     }
     
     
     

     

3. Save and Apply:

   • After creating the custom visual, save it.
   • Apply it to your Discover report.
   • Ensure you repeat the dropzone selections as outlined in Step 1 to maintain the same scenario in your visual.

   • 

   • With date range selection

     

The flexibility of custom visual scripting allows you to use various chart types and libraries like D3.js to tailor the visualization to your preferences.

Thanks,

Raja Sambasivam