Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How can generate this report?

Untitlednew.png

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

Thank you.

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

Re: How can generate this report?

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:

View solution in original post

5 Replies
Highlighted
Master II
Master II

Re: How can generate this report?

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:

View solution in original post

Highlighted

Re: How can generate this report?

Hi,

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

QlikCommunity_Thread_305123_Pic1.JPG

QlikCommunity_Thread_305123_Pic2.JPG

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

QlikCommunity_Thread_305123_Pic3.JPG

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

Highlighted
Contributor III
Contributor III

Re: How can generate this report?

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?

Untitlednew.png

Thank you.

Highlighted
Master II
Master II

Re: How can generate this report?

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

Highlighted
Contributor III
Contributor III

Re: How can generate this report?

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

Thank you once again for help.