Forum Discussion

Mikolajszewczyk's avatar
Mikolajszewczyk
Regular Visitor
8 months ago
Solved

Filtering chart by chart and by slicer at the same time - issue

Hi, recently at my work I was working on a pretty specific report and came across an interesting issue connected to filtering a chart with another chart and a slicer at the same time. I've created a ...
Need Help
Rupak_bi's avatar
Rupak_bi
Icon for Super User rankSuper User
8 months ago

Hi Mikolajszewczyk ,

 

Can yoy please share the sample data. in my view, the calculation is getting wrong based on how the percentage calculated and how the characteristic is mapped in the table for each events.

Mikolajszewczyk's avatar
Mikolajszewczyk
Regular Visitor
7 months ago

Hi rupak,

I dont see an option to send data here, so i think the easiest way would be to give you code to create it by yourself. I have used a excel makro to create it. Here is a sample code:

Sub Generate60000()

    Const ROWS_COUNT As Long = 60000
    Const STATUS_COLS As Long = 5
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)
    ws.Cells.Clear
    
    ws.Range("A1:G1").Value = Array( _
        "customer_number", _
        "status1", "status2", "status3", "status4", "status5", _
        "characteristic")
    
    Dim data()
    ReDim data(1 To ROWS_COUNT, 1 To 7)
    
    Dim i As Long, c As Long
    Dim allEvent1 As Boolean
    Dim ch As String
    
    Randomize
    
    For i = 1 To ROWS_COUNT
        
        data(i, 1) = i   ' customer_number
        allEvent1 = True
        
        ' Statusy
        For c = 1 To STATUS_COLS
            data(i, 1 + c) = "event" & Int(Rnd * 4) + 1
            If data(i, 1 + c) <> "event1" Then allEvent1 = False
        Next c
        
        If allEvent1 Then
            ch = "characteristic" & Int(Rnd * 4) + 1   ' 1–4
        Else
            ch = "characteristic" & Int(Rnd * 5) + 1   ' 1–5
        End If
        
        data(i, 7) = ch
        
    Next i
    
    ws.Range("A2").Resize(ROWS_COUNT, 7).Value = data

It creates 60k of records, and also makes sure that one path - event (1-1-1-1-1) doesnt not have a representative in the characteristics5