How can generate this report?

[sadrisalihu]

Please, any suggestion how can we calculate this report in this format.

Thank you.

[Frank_Hartmann · Accepted solution]

Script:

Island_Table:
Load * Inline [
    dim
    1
    2
    ];

Table1:
LOAD * inline [
Year,    Amount,    Invoice    , Category
2018,    12000,    200    ,1
2018,    345,    30    ,2
2017,    10000,    150    ,1
2017,    1342,    50    ,2
];

Pivot:

Dimension1

=pick(dim,Category,'Total')

Textformat Dimension1

=if(pick(dim,Category,'Total')='Total','<B>')

Textcolor Dimension1

=if(pick(dim,Category,'Total')='Total',LightRed())

Dimension2

=ValueList($(vMaxYear),$(vMaxYearBef),'index')

Textcolor Dimension2

=if(pick(dim,Category,'Total')='Total',LightRed())

Textformat Dimension2

=if(pick(dim,Category,'Total')='Total','<B>')

Textformat Expression1

=if(pick(dim,Category,'Total')='Total','<B>')

Textcolor Expression1

=if(pick(dim,Category,'Total')='Total',LightRed())

Textformat Expression2

=if(pick(dim,Category,'Total')='Total','<B>')

Textcolor Expression2

=if(pick(dim,Category,'Total')='Total',LightRed())

Sort Tab:

first expression:

Numeric Value Descending

second expression:

State: Ascending

Text A->Z

Presentation Tab:

deselect "Show Partial Sums"

Output:

[MarcoWedel]

Hi,

one solution that does not require changing your data model might be:

=ValueList($(=Concat(DISTINCT Chr(39)&Category&Chr(39),',')),'Total')

If(Dimensionality()=2,
   Money(Sum(If(Match(ValueList($(=Concat(DISTINCT Chr(39)&Category&Chr(39),',')),'Total'),Category,'Total'), Amount))), 
   Num(Sum({$<Year={'$(=Max(Year))'}>} If(Match(ValueList($(=Concat(DISTINCT Chr(39)&Category&Chr(39),',')),'Total'),Category,'Total'), Amount))
       /
       Sum({$<Year={'$(=Max(Year,2))'}>} If(Match(ValueList($(=Concat(DISTINCT Chr(39)&Category&Chr(39),',')),'Total'),Category,'Total'), Amount)),'0,00%'
      )
  )

hope this helps

regards

Marco

[sadrisalihu]

Thank you Frank Hartmann for your answer, but if I have more than one category I'm showing a new blank line.

Do you have any idea how to remove this line?

Thank you.

[Frank_Hartmann]

What happens if you supress null values for the  first dimension in the dimension tab?

[sadrisalihu]

Exactly, if I uncheck the Suppress When Value field is Null, the empty line is deleted.

Thank you once again for help.