Incoherent summary when period is quarters, year or even YTD

rfilip
rfilip
in Modeling

Hello,

I'm really at the beginning of the journey of being Model Builder so I'm afraid that what I'm to ask you is really basic, but hope to find help and the learn more :).

The context of the issue is that the following line item is looking for the customers that have 0 revenue in the current month, and if is true, then it shows the revenue from the previous month.

Month by month it is coherent, but when the period is a quarter, FY or even YTD, it explodes and so far from the reality.

I've tried multiple combinations of summaries, but no luck until now, to discover how to adjust the formulas, in order to simply add the monthly amounts and not check if the condition is met.  

rfilip_0-1650023515010.png

rfilip_1-1650023657639.png

 

Thank you !

Tagged:

Answers

  • TimothyThomas
    TimothyThomas

    Hi @rfilip ,

     

    Do not be afraid to ask here, no matter how basic! From your screenshots, your line items seem to make logical sense, and I would expect the default summary method to work for what you are describing. Could you possibly attach a screenshot of the line item in default view so we can see the problem occurring?

    Thanks,

    Timmy

  • ManjunathKN
    ManjunathKN

    Hi

    Have you tested with time summary as closing Balance?

    let me know if that did not work for you. we can use ratio as summary to resolve the issue.

    Thanks,
    Manjunath

  • rfilip
    rfilip

    Hello @TimothyThomas,

    Thanks in advance for helping me !

    > Example for Jan 22, Up sell has 7 ;

    rfilip_4-1650264030843.png

    > For Feb the same line has 7, but recorded as being recurring;

    rfilip_5-1650264084230.png

    According to conditions set, both Jan and Feb are correct.

    > However, Mar 22 data is not available, so the Revenue in current Month = 0, so the formula works fine and it returns the 7 from the last month, as being loss revenue in Mar 22. But this has an huge impact on the quarter summary, year and so on. 

    rfilip_6-1650264221594.png

    > Q1 22, shows the 2 correct amounts for Jan and Feb, but it also shows the amount for Mar (which is not real).

    rfilip_7-1650264353395.png

     

     So now I'm thinking that maybe I should add a condition on the period, to check if there is data or not, before calculating the amounts. Any help, of how I can do that, will be extremally appreciated it. 

     

     

  • rfilip
    rfilip

    Hello ManjunathKN,

    Thank you for your answer !

    The summary I need is to have JAN + FEB + MAR = quarter 1, so from my understanding this option will show only the value for MAR.

  • ManjunathKN
    ManjunathKN

    Hi 

    Create 3 Line items,

    1. Loss Revenue (-) Sum Data - formula -> If Up Sell>0 then Loss Revenue (-) else 0
    2. Loss Revenue (+) Sum Data - formula -> if up sell>0 then Loss Revnue (+) else 0
    3. Denominator - Formula - "1"

    All three are number formatted line item.

    now, in your Loss Revenue (-) line item, Set Time summary as Ratio -->New line item 1/New line item 3
    in your Loss Revenue (+) line item, Set Time summary as Ratio-->New line item 2/New line item 3.

    Hope this helps,

    Thanks,
    Manjunath

Categories