How can we help?

New post
0

Facing issue in Merging Pagination and Series formula

Avatar
Roma Chandani
None
Follow

Hey, I am new to Klipfolio and I am not able to merge my series formula and Pagination formula. Please Suggest me what to do.

Pagination - ARRAY(JOIN(Map(CUMULATIVE ( REPEAT ( 1, ( CEILING (@/TotalCount;/100)))) ,"$pageNo", JOIN())))

Series -  Array(
sum(select(@/Items/DurationInSeconds,and(contains(!'5b957612',"East"),BETWEEN(!'3908846c',"03:00:00Z","04:00:00Z"))))

Date Votes

3 comments

  • 0
    Avatar
    Janice Janczyn

    Hi Roma,

    Welcome to Klipfolio! 

    To start, you can simplify your pagination formula from

         ARRAY(JOIN(MAP(CUMULATIVE ( REPEAT ( 1, ( CEILING (@/TotalCount;/100)))) ,"$pageNo", JOIN(formula))))

    to

         MAPFLAT(CUMULATIVE ( REPEAT ( 1, ( CEILING (@/TotalCount;/100)))) ,"pageNo", formula )

    See the MAP and MAPFLAT article for details. Also, note that you should not type the $ in the 2nd parameter of the MAPFLAT, instead type only the variable name between the quotes. 

    Your series formula would be something like (I like to use separate lines and align the parameters for readability):

         MAPFLAT(CUMULATIVE ( REPEAT ( 1, ( CEILING (@/TotalCount;/100)))) ,
                          "pageNo",
                         SUMIF( AND( CONTAINS(@data, "East"), BETWEEN( @/dates,"03:00:00Z","04:00:00Z")),
                                      @/Items/DurationInSeconds, ) )

    Notes

    • this example assumes the data in your paginated datasources are in @/dates and @/data. If you don't need to use references, it's preferable to refer to the data directly as it is more readable and can often result in a more efficient formula
    • use SUMIF(condition, data)  instead of SUM( SELECT( data, condition ) )

    I hope this helps!

    Thank you,
                  Janice

     

     

  • 1
    Avatar
    Roma Chandani

    Thanks Janice, but still i am facing issue with this when i try to use variable in string without $ sign it is giving me error. do i need to use this mapflat formula only on series or on both series as well X-axis..??

    HTML Template(Data : Hour) : SUBSTRING(@/Items/StartTime,11,13)

    My X- Axis : GROUP(Data : Hour)

    Series : 

    LOOKUP(
    !X-Axis : Hour,
    Group(
    SELECT(
    !Data : Hour,
    LOOKUP(@kf:fill_elements(/Items,'ClientName'), @/records/Name;, @/records/Time_zone_for_live_calls__c;) = "Extended hours - Mon-Fri 5:30 am Pacific - 9:00 PM Pacific, Sat & Sun 8:30 am Pacific - 5:00 pm Pacific"
    )
    ),
    GROUPBY(
    SELECT(
    !Data : Hour,
    LOOKUP(@kf:fill_elements(/Items,'ClientName'),@/records/Name, @/records/Time_zone_for_live_calls__c) = "Extended hours - Mon-Fri 5:30 am Pacific - 9:00 PM Pacific, Sat & Sun 8:30 am Pacific - 5:00 pm Pacific"
    ),
    SELECT(
    @/Items/DurationInSeconds,
    LOOKUP(@kf:fill_elements(/Items,'ClientName'),@/records/Name,@/records/Time_zone_for_live_calls__c;) = "Extended hours - Mon-Fri 5:30 am Pacific - 9:00 PM Pacific, Sat & Sun 8:30 am Pacific - 5:00 pm Pacific"
    )
    )
    )

    I have 50000 records and 53 pages ,When I am using only formula it is working fine but when i try to use pagination MAPFLAT(CUMULATIVE ( REPEAT ( 1, ( CEILING (@/TotalCount;/100)))) ,"pageNo", formula )) it is giving me error.

     please suggest how to solve this pagination issue

  • 0
    Avatar
    Janice Janczyn

    Hi Roma, 

    The MAPFLAT formula is very likely timing out because of the large number of pages. You will have to somehow reduce the number of pages, either by limiting the timeframe returned or retrieving aggregated data. To verify the MAPFLAT works, try replacing the first parameter with 

       CUMULATIVE ( REPEAT ( 1, 10 ) )  

    For further assistance, please email support@klipfolio.com and let us know what type of datasource you are using.

    Thank you,
                Janice

Please sign in to leave a comment.