topic Re: How can generate this report? in QlikView

How can generate this report?

sadrisalihu — Sun, 17 Jun 2018 17:43:29 GMT

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

Thank you.

Re: How can generate this report?

Frank_Hartmann — Sun, 17 Jun 2018 18:54:30 GMT

Script:

Island_Table:

Load * Inline [

dim

];

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:

Re: How can generate this report?

MarcoWedel — Sun, 17 Jun 2018 20:32:40 GMT

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

Re: How can generate this report?

sadrisalihu — Sun, 17 Jun 2018 20:57:56 GMT

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.

Re: How can generate this report?

Frank_Hartmann — Sun, 17 Jun 2018 21:18:50 GMT

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

Re: How can generate this report?

sadrisalihu — Sun, 17 Jun 2018 21:28:28 GMT

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

Thank you once again for help.