Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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)))

Anonymous
Not applicable
Author

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!

sunny_talwar

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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.