How to limit the values showed without altering values?

[jirazazabal]

Hi,

We have some scenarios when we need to calculate some metrics based on all the history. For example, contribution and more. We need to include several data to get the right value. But, for example, if we have an history of 5 years, we need to perform some calculations on the whole data, but the user wants to see only the last year information. But this information is calculated by taking all the history. If I apply a filter, I'm able to see current year data, but the values are not correct, because I need all the history (for example, churn rate history). Is there a way to perform that in the widget?

Thanks!

[harikm007]

Hi @jirazazabal ,

If you need to get values from all dates, you can use ALL function (https://documentation.sisense.com/docs/function-references#all).

For example, to get total Sales Amount from all purchase date, use below formula

(SUM([Sales Amount]), ALL([Purchase Date]))

Is that what you are looking for?

-Hari

[jirazazabal]

Hi @harikm007 

I've tried to do it, but in contribution, YTD... seems desn't work. I get en ERROR in the FOrmula when appling this calc, or I get no data in all the columns...

Thanks

[harikm007]

Can you share the formula you were trying and the actual calculation you need?

-Hari

[jirazazabal]

Two metrics:

YTDSum([Total Bin_Suscrip_Act_Instal (1)])

Contribution([Valor (1)])

[harikm007]

Here, instead of using 'Contribution' function, try calculating the contribution by using division operator:

If the formula is 'Contribution(SUM([Valor (1)]))', change it to :

SUM([Valor (1)])/(SUM([Valor (1)]), ALL([Date]))

-Hari