Filter Based on Percentage Change of Chart

Josh_Evans20
Josh_Evans20 Member
in Charting

I'm trying to filter out any changes above X%

This is from the Flex table. If I need to create a new field how would I calculate that?

image.png

Best Answer

  • Manasi_Panov
    Manasi_Panov Coach
    Answer ✓

    Hi @Josh_Evans20,

    Here's an example of calculating the percentage difference in Beast Mode:

    (ZZ_VALUE_THIS_M-ZZ_VALUE_LAST_M)/((ZZ_VALUE_THIS_M+ZZ_VALUE_LAST_M)/2)

    This month's turnover (ZZ_VALUE_THIS_M):

    SUM(CASE WHEN YEAR(CURRENT_DATE()) = YEAR(Date) AND MONTH(CURRENT_DATE()) = MONTH(Date) THEN Parts Kits Value ELSE 0 END)

    Previous month's turnover (ZZ_VALUE_LAST_M):

    SUM(CASE WHEN YEAR(CURRENT_DATE()) = YEAR(Date) AND MONTH(CURRENT_DATE())-1 = MONTH(Date) THEN Parts Kits Value ELSE 0 END)

    Filter formula (ZZ_FILTER). You can customize it to suit your requirements. You can also implement a slider variable or free input variable to easily select the filtering value for the difference.

    CASE
    WHEN (ZZ_VALUE_THIS_M-ZZ_VALUE_LAST_M)/((ZZ_VALUE_THIS_M+ZZ_VALUE_LAST_M)/2) > 0 THEN 'Above'
    ELSE 'Below'
    END

    Here is the result using the HTML table:

    image.png

    You can adjust the table by selecting the filter:

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • Manasi_Panov
    Manasi_Panov Coach

    Hello @Josh_Evans20,

    I couldn't achieve this with the Flex table, but it's possible with other cards like the Mega table, Line graph, Bar chart, and so on. In all instances, you'll need to perform your Difference calculation in Beast Mode and then filter based on the created calculation field.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • Josh_Evans20
    Josh_Evans20 Member

    How does a percentage change work in a beast mode? As in taking last month and this month to get the change?

  • Manasi_Panov
    Manasi_Panov Coach
    Answer ✓

    Hi @Josh_Evans20,

    Here's an example of calculating the percentage difference in Beast Mode:

    (ZZ_VALUE_THIS_M-ZZ_VALUE_LAST_M)/((ZZ_VALUE_THIS_M+ZZ_VALUE_LAST_M)/2)

    This month's turnover (ZZ_VALUE_THIS_M):

    SUM(CASE WHEN YEAR(CURRENT_DATE()) = YEAR(Date) AND MONTH(CURRENT_DATE()) = MONTH(Date) THEN Parts Kits Value ELSE 0 END)

    Previous month's turnover (ZZ_VALUE_LAST_M):

    SUM(CASE WHEN YEAR(CURRENT_DATE()) = YEAR(Date) AND MONTH(CURRENT_DATE())-1 = MONTH(Date) THEN Parts Kits Value ELSE 0 END)

    Filter formula (ZZ_FILTER). You can customize it to suit your requirements. You can also implement a slider variable or free input variable to easily select the filtering value for the difference.

    CASE
    WHEN (ZZ_VALUE_THIS_M-ZZ_VALUE_LAST_M)/((ZZ_VALUE_THIS_M+ZZ_VALUE_LAST_M)/2) > 0 THEN 'Above'
    ELSE 'Below'
    END

    Here is the result using the HTML table:

    image.png

    You can adjust the table by selecting the filter:

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories