topic Re: Set analysis formula not working in pivot table in App Development

Set analysis formula not working in pivot table

Ed — Fri, 02 Apr 2021 07:51:55 GMT

Hello,

I'm trying to create a pivot table with a measure in line and a hierarchy dimension in column but the value returned when the conditions are completed are just Sum(Sales). Here is my set analysis formula :

if(Annee = Year(Today()),if( Mois = Num(Month(Today())),
(
Sum({$<Annee = {"$(=Year(Today()))"}, Mois = {"$(=Num(Month(Today())))"}>}Sales)
*Sum({1<Annee = {"$(=Year(Today()))"}, Mois = {"$(=Num(Month(Today())))"}>}openDays)
/Sum({$<date_jour = {">=$(=monthstart(Today()))<=$(=max({$<Sales-={'0'}>}date_jour))"}> }openDays)
)
,sum(Sales))
,sum(Sales))

Do you have an idea how to fix this ?
Thanks

Re: Set analysis formula not working in pivot table

Anders_Eriksson — Mon, 18 Mar 2019 08:35:29 GMT

That is expected from your formula because the following part is probably evaluated to "1".
Both probably evaluating to the number of open days in current month.

Sum({1<Annee = {"$(=Year(Today()))"}, Mois = {"$(=Num(Month(Today())))"}>}openDays)
/Sum({$<date_jour = {">=$(=monthstart(Today()))<=$(=max({$<Sales-={'0'}>}date_jour))"}> }openDays)

You expect this to yield different values per table cell?
Not so as the set analysis is evaluated once on the data model before traversing the table cells.
Dimensions in table does not influence the set analysis.
After that the dimensions are applied to the resulting data model output from the set analysis.
Common mistake of people beginning with set analysis to think it will yield different results depending on table dimensions.

Re: Set analysis formula not working in pivot table

Ed — Mon, 18 Mar 2019 10:37:29 GMT

Hi,

Thank you for your response. Yes I expected this to yield different values per table cell, because my formula is giving the good value when I use the KPI visulazation and the pivot table filters, on the hierarchy dimension, the Sum(Sales) formula which is a set analysis function also.

My goal here is to display the Sum(sales) for all the periods before today month and year and for the current value of Sales do a basic calcul which is :

Sum(Sales)*(open days in the current month ofthe current year) /(the open days allready past)
filtered on a hierachy dimension in a pivot table

There isn't any way to show that ?

Thanks

Re: Set analysis formula not working in pivot table

Ed — Mon, 18 Mar 2019 13:53:41 GMT

Hi,

It appears, indeed, that the following formula doesn't display the right value.
Sum({1<Annee = {"$(=Year(Today()))"}, Mois = {"$(=Num(Month(Today())))"}>}openDays)

It gives 6 just like this formula (my current number of opendays) :
Sum({$<date_jour = {">=$(=monthstart(Today()))<=$(=max({$<Sales-={'0'}>}date_jour))"}> }openDays)

instead of 21 the number of days on the month. Can't I use an aggregate function or something else to make it work in the pivot table ?

Thanks

Re: Set analysis formula not working in pivot table

Ed — Mon, 18 Mar 2019 14:52:38 GMT

Hi,

I found the solution :

Sum(total{$<Annee = {"$(=Year(Today()))"}, Mois = {"$(=Num(Month(Today())))"}>}openDays)

Have a nice day everyone 🙂