LISS with formulas

kristenr

Hi,

I have an issue where if I use a LISS to collect a line item that has a formula summary, it sums the line item for the time dimension rather than adopting the formula. Any assistance would be great. Thanks in advance.

veerendra

Hi,

It should ideally work as long as you've setup Summary as 'Formula' for the target line item as well where you wrote Collect() formula.

Example:

shivanandhonnalli

Hi, Can you just change the summary method to sum instead of formula.

kristenr

Thanks @veerendra - that worked for one line item! Thank you. But having issues with my other line items as I am trying to use a lookup which will not let me use formula but if I use sum then it sums rather than applies the formula. I tried a work around but this also didn't work.

veerendra

Yes, You can't setup summary as Formula while using Lookup. I can comment more if I know I can get more context, but one way to achieve is to eliminate using Lookup/sum in the formula so that you can use Formula as Summary
See if you can use If else condition instead of a lookup (i.e, bringing Summary value separately in a different line item and connecting both line items using If Else condition to get the final one).

kristenr

Thanks @veerendra

I have a list of fake versions of P&L reports (Sep Board Report, August Board Report) and I want to be able do a variance analysis between the versions of reports. So I used a line item subset of the main P&L report which has performance metrics using formulas (ie. Revenue / Cost %). The main P&L report is dimensioned by the fake versions. I want to create a variance analysis module to compare the values between two fake report versions so:

This Report (Sep Board Report) // Last Report (August Board Report) // Variance

I want to show this for H1FY24, H2FY24 and FY24 but I am having trouble looking up the values from the Last Report while maintaining the formula on the performance metrics at the FY24 level.

Thank you!

veerendra

You can do it in 2 steps

(a) bring Revenue to the Module (Line Item Summary; Sum); Cost Line Item (Line Item Summary: Sum)

(b) Then you can build a line item (Revenue/Cost%) with summary formula

In Step (a) you can use lookups as the summary is set to sum and in step (b) you won't have to use lookups as you already got the relevant data with right dimensions.

Let me know it is confusing.

Thanks.

kristenr

Yes, so sorry I am not quite sure how to do this practically with using the line item subset. I used the line item subset so I could have the P&L line items as the line item subset (ie. LIS: PNL01) and This Report, Last Report and Variance as the line items.

Here are my modules (that I simplified) - the issue is the FY operating profit % which is not calculating correctly with using the line item subset.

Profit and Loss Report module

Variance Analysis module (using PNL01 as line item subset)

System Module to look up last report

Issue with FY24 summing the operating profit margin % rather than applying a formula: