10 Replies Latest reply: Jan 27, 2016 6:46 PM by Edgar Vazquez RSS

    Above() function- need help

      Hello,

       

      I have rolling totals. I use above() function to get non rolling (just for that week) values. For e.g if week 1 has 5 count and week 2 has 10 (5+5 new) then I use the following expression to get just 5 for week 2. The logic fails when I have 0 for a week. e.g week 3. then in week 4 , I want to do week 4 count(cumulative) - week 2 count(cumulative) instead of week 4 count(cumulative) - week 3 count(cumulative). If several weeks in a row have 0 count I should be subtracting to last non zero count.

       

      Sorry for being so confusing. Attaching the screenshot. A picture is worth a thousand words..

       

      Thanks

      DV

        • Re: Above() function- need help

          =If(IsNull(IF(isnull(above([NT])) , [NT], $(vExpAnnAggr(sticky_count))-above([NT]))) or IF(isnull(above([NT])) , [NT], $(vExpAnnAggr(sticky_count))-above([NT])) < 0, 0, IF(isnull(above([NT])) , [NT], $(vExpAnnAggr(sticky_count))-above([NT])))

           

          where NT - =$(vExpAnnAggr(sticky_count))

          • Re: Above() function- need help

            Anyone can help please?

              • Re: Above() function- need help
                Edgar Vazquez

                Could you send us a QVW sample?

                 

                 

                  • Re: Above() function- need help

                    Edgar, unfortunately I am not able to send qvw as it has sensitive info.

                     

                    Following is some more information if that helps..

                    I have shown values on data points. If you notice 2nd data point, it says 10 but actually it is 10-5 = 5 (plotted at 5). this is a correct behavior. Any time the value hits 0, the next data point like 24 (shown in screenshot), it does 24- 0 = 24. This behavior is consistent with what is done in exp, but what i actually want is it should subtract the last non zero value. i.e 24-10 =14. I have used above to just go one step behind. I want to change that to a dynamic offset based on count of zeroes we have.

                     

                    SET vExpAnnAggr = "aggr(sum(aggr(max({$<metric_type={'daily_usage'}>} $1),WeekStart,product_id)),WeekStart,user_id)"

                     

                    NT = $(vExpAnnAggr(sticky_count))

                     

                    =If(IsNull(IF(isnull(above([NT])) , [NT], $(vExpAnnAggr(sticky_count))-above([NT]))) or IF(isnull(above([NT])) , [NT], $(vExpAnnAggr(sticky_count))-above([NT])) < 0, 0, IF(isnull(above([NT])) , [NT], $(vExpAnnAggr(sticky_count))-above([NT])))

                      • Re: Above() function- need help
                        Edgar Vazquez

                        ammmmm ok in this example:

                         

                        Month,     Value

                        1               100

                        2               500

                        3               800

                        4             1000

                        5             1300

                        6             1700

                        7             2000

                        8             2000

                        9             2000

                        10           2200

                        11           2600

                        12           2800

                        ___________________________________________

                         

                        You wanna this result:

                         

                        Month       Expression

                        1                 100

                        2                 400

                        3                 300

                        4                 200

                        5                 300

                        6                 400

                        7                 300

                        8                     0

                        9                     0

                        10                   0

                        11              400

                        12              200

                         

                         

                        I´m right???

                          • Re: Above() function- need help

                            I was just putting a similar example for you to be able to help..

                            note changes in bold,

                             

                            Month,     Value

                            1               100

                            2               500

                            3               800

                            4             1000

                            5             1300

                            6             1700

                            7             2000

                            8             0

                            9            0

                            10          0

                            11           2600

                            12           2800

                            ___________________________________________

                             

                            You wanna this result:

                             

                            Month       Expression

                            1                 100

                            2                 400

                            3                 300

                            4                 200

                            5                 300

                            6                 400

                            7                 300

                            8                     0

                            9                     0

                            10                   0

                            11              600

                            12              200

                            • Re: Above() function- need help

                              Adding sample qvw

                              So where you see 24, I need 14.