How can we help?

New post
0

Cumulative not filtering by User Input Control

Avatar
Fabio Farah
None
Follow

Hi, 

I have two datasets: one with the daily goal for the number of leads and the other one with the daily number of leads per type (there are 3 types os leads). 

I've then created a klip that brings the daily number of leads and the percentage of the goal that day for a specific month, defined by a User Input Control. This works fine but when I want to bring make a cumulative klip for the selected month, it acumulates the date since the start of the dataset, and not within the month.

E.g.:

Dataset Goals example: @goals

Month       Day           Goal
2021-01     2021-01-01    100
2021-01     2021-01-02    120
2021-01     2021-01-03    90
...
2021-12     2021-12-30    130
2021-12     2021-12-31    60

Dataset Results example: @results

Month       Day           Type   Leads
2021-01     2021-01-01    A      60
2021-01     2021-01-01    B      50
2021-01     2021-01-02    A      80
2021-01     2021-01-02    B      40
...
2021-12     2021-12-31    A      70
2021-12     2021-12-31    B      60

And I have an User Input Control 2021-01 to 2021-12 and put Hidden Data Filters that match the user input in each chart.

For the simple chart I use the X axis as @goals, B:B and the series as

LOOKUP(!X Axis: Data, group(results,@B:B;), GROUPBY(results,@B:B, results@D:D;))

and it works fine.

For the cumulative chart I tried 

LOOKUP(!X Axis: Data, group(results,@B:B;), CUMULATIVE (GROUPBY(results,@B:B, results@D:D;)))
and
CUMULATIVE (LOOKUP(!X Axis: Data, group(results,@B:B;), GROUPBY(results,@B:B, results@D:D;)))

Both work for the first month but for the second it brings all the cumulative results for previous months.

 

Any suggestions on how to solve this?

Thanks in advance.

Date Votes

2 comments

  • Avatar
    Parker Selman Official comment

    Hi Fabio, 

    Thanks for the post! 

    Based on the information you've provided here, it looks like the second CUMULATIVE formula you have written should be returning the right values. I may need a bit more information on that formula and the data in it in order to help diagnose the issue. 

    Based on your post, I'm assuming that your X axis contains daily dates from column B, and you're using a hidden data filter to ensure that those days match the month specified in your user input control. If that's the case, then the 2nd formula (CUMULATIVE (LOOKUP(!X Axis: Data, group(results,@B:B;), GROUPBY(results,@B:B, results@D:D;)))) should be working like so:

    1) Inputting each daily value on the X axis (filtered to only the month in the user input control)
    2) Matching those daily values against column B of results, grouped
    3) Returning the sum of column D for those matched days
    4) Displaying only those matched day sums cumulatively. 

    In theory, this should only be returning cumulative results for the month specified in your user input control. If this isn't what's happening, then perhaps I'm missing some information about your X axis or filtering setup that's preventing this from working as expected. Please let me know any additional information you can about your Klip setup here, or send us an email at support@klipfolio.com to create a ticket with our support team, where we'd be able to take a deeper dive into your issue to come to a resolution. 

    Let me know if you have any questions! 

    Parker

  • 0
    Avatar
    Fabio Farah

    Hi, Parker.

    Thanks for the quick response!

    I was thinking that maybe the problem is becaus my X axis is based on the @goals database. I tried to base it on the @results but I had trouble getting the groupings right. 

    I've sent an email to support and I'll keep trying.

    Thanks.

Please sign in to leave a comment.