Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

henry_tran
New Contributor II

Pivottable compare Columns

Hi all,

I am struggling with a comparison between columns in a pivot table.

1.JPG

In the columns I have years as well as months. What I want to do is compare the values from one year to the year before.

Right now I am using "before" but it only works with the years. When I open the years up into months it won't compare the month of 2017 with the same month of 2016.

I tried something like this

if(sum ( {< DF_BRUTTO_NETTO = {'Anwesend'} > }BU_BU_WERT_SUM) > 0,

sum ( {< DF_BRUTTO_NETTO = {'Anwesend'}, Flag = {'2017'} > }BU_BU_WERT_SUM)

/

sum ( {< DF_BRUTTO_NETTO = {'Anwesend'}, Flag = {'2016'} > }BU_BU_WERT_SUM) -1)

but it's not showing me the correct results.

I have attached an example.

Thank you for your help.

5 Replies
MVP
MVP

Re: Pivottable compare Columns

I see that you have AsOfTable in your app, why don't you use that in your chart? If you don't want to use it, then you can use an expression like this (will need to be fine tuned based on your exact requirement)

If(SecondaryDimensionality() = 1,

Sum( {< DF_BRUTTO_NETTO = {'Anwesend'} >} BU_BU_WERT_SUM) /

after(Sum( {< DF_BRUTTO_NETTO = {'Anwesend'} >} BU_BU_WERT_SUM))-1,

If(SecondaryDimensionality() = 2,

Aggr(Sum( {< DF_BRUTTO_NETTO = {'Anwesend'} >} BU_BU_WERT_SUM) /

Below(Sum( {< DF_BRUTTO_NETTO = {'Anwesend'} >} BU_BU_WERT_SUM))-1, DF_UNTERNEHMUNG, DD_MONAT, DD_JAHR)))

henry_tran
New Contributor II

Re: Pivottable compare Columns

Hi Sunny,

nice to have your help again

I have some questions to your solution.

There are some issues with it.

The color expressions don't seem to work anymore if I open it up into months.

Capture.JPG

As you can see there are negative numbers with a green color?

Also when I open it up into months the %-differance of totals is disappearing (they are replaced with "-").

Could you show me a solution with the AsOfTable-table? (You showed me the function of it about 2 months back)

Thank you!

MVP
MVP

Re: Pivottable compare Columns

I will check, but that might have to do with the expression used for Color. Are you using color expression?

henry_tran
New Contributor II

Re: Pivottable compare Columns

yes I am, you should be able to see that in the app.

But the solution from you has one major issue. If I break it up into months the %-difference in totals disappears somehow. I have marked it in blue.

Capture (1).JPG

Actually the table should also show the %-difference in totals.

MVP
MVP

Re: Pivottable compare Columns

I know this issue, and that is why I suggested AsOfTable. I won't have enough time to look at this right now, but as soon as I get time, I will take a look. Will work on AsOfTable first, will worry about colors later.