Can I create a pivot table using values from another pivot table?

Options
Zel
Zel Member
edited March 2023 in Cards, Dashboards, Stories

Hello, please I need help here.

I have a pivot table [IMG1] with the following data:

  1. Alarm ID
  2. No. of attempts before resolving (or basically number of attempts associated with the alarm ID)
  3. SF Alarm Count ('1', since we only have 1 alarm per alarm ID)

[IMG1]

image.png

So here's the output that we want to achieve [shown in IMG2]:

  1. Column 1: will show the number of attempts before resolving
  2. Column 2: will show the number of alarms resolved with the number of attempts in column 1.

For example: based from the image above:

We have 1 alarm resolved after 45 attempts.

We have 2 alarms resolved after 30 attempts.

We have 1 alarm resolved after 20 attempts

We have 2 alarms resolved after 19 attempts.

We have 3 alarms resolved after 15 attempts, etc.

3. Column 3 = Column 1 x Column 2

[IMG2]

image.png

Please help me and thank you in advance.

Answers

  • JasonAltenburg
    JasonAltenburg Coach
    Options

    In IMG1 is the reason it is a pivot table that you are summing column 2?

    I would suggest using Magic ETL and a group by to create the data as it is in IMG1, then it is as simple as creating a beast mode to multiply your columns:

    image.png
    image.png

    I'm making assumptions based on what I think the shape of your data is to provide the sample ETL below, if this doesn't work please provide some sample data.

    Assumed data shape:

    image.png

    Group by magic etl:

    image.png

    Data output, note you could also add a constant column to get your '1' for each row if so inclined:

    image.png

Categories