8 Replies Latest reply: May 10, 2017 7:06 AM by bruno bertels

# Qlik Sense - calculating difference and difference percentage

I use Qlik Sense.  I'm fairly new to it and have not attended all the training classes yet.  Online, someone suggested the Previous() function to accomplish this, but I can't find a good example on how to use it.  I think I have to use it in the load data script??

• ###### Re: Qlik Sense - calculating difference and difference percentage

Hi Celia

Previous() might be a solution depending of what is your desired output.

Calculating difference but wich one ? is it a difference with a previous period ( Day Week Month year ) or with previous row , or with previous selection ?

May I sucgest you to upload a sample of your data in excel and your desired output , so that the community will help you easily in what you want to achieve

• ###### Re: Qlik Sense - calculating difference and difference percentage

Here is a sample of the data in the billing table.  I want to compare the billed field; it would be comparing previous year and month.  The data has contains 4 school years so far.

 school_year tyear tmonth tmonthname schoolid disciplineid therapistid studentid billed 2013-2014 2013 8 August 11169 66 7049 14006 46.5 2013-2014 2013 8 August 11169 66 7049 14038 46.5 2013-2014 2013 8 August 11177 66 7049 13940 31 2013-2014 2013 8 August 11252 65 7102 13784 20.66667 2013-2014 2013 8 August 11252 65 7102 13845 20.66667 2013-2014 2013 8 August 11252 65 7102 14022 31
• ###### Re: Qlik Sense - calculating difference and difference percentage

Hi

OK , what is your desired output ? a TCD with the different years in column and side by side a column "difference"

A straight table , or a graph ?

Bruno

• ###### Re: Qlik Sense - calculating difference and difference percentage

I generally use pivot tables.  I don't know what TCD means.

• ###### Re: Qlik Sense - calculating difference and difference percentage

I've attached a pdf of one of my pivot tables.

• ###### Re: Qlik Sense - calculating difference and difference percentage

Oups , sorry a TCD is a Pivot Table , this is the acronyme in french for "Tableau Croisé Dynamique" ( Dynamic Cross Table ) Pivot table in English ..

I see now what you need

In your Pivot Table you may have

Dimension in row : Discipline

Dimension in column School Years

Then as mesure you have sum(Billed)

To have the différence with previous school year :

Add this mesure sum(billed) - before(sum(billed))

To have the différence in % :

(sum(billed) - before(sum(billed))) / before(sum(billed))

so that Under each school years columns you will have 3 mesures The amount the difference with previous year and the difference in ratio format.

So in a TCD use the before() statement , in straight table use above() statement

Hope it helps

If it doesn't work properly post a sample data with 2 different school years a i will built our Pivot Table

• ###### Re: Qlik Sense - calculating difference and difference percentage

Thank you Bruno, this is working, however, for the earliest year, there is nothing to compare with and the 2 new columns show blank.  This is ok for now, unless you have a suggestion for hiding them in such a case.   Thank you again, this was much easier than I thought it would be.

By the way, if you are from France, congratulations on your new President  I envy France and Canada; most of us here in the U.S. HATE OUR PRESIDENT.

Celia

• ###### Re: Qlik Sense - calculating difference and difference percentage

Hi Celia

You can't hide colums if they are blank

but you can replace the blank value - with a text or an integer :

if(isnull(

sum(billed) - before(sum(billed))

) , 'NOTHINK' ,

sum(billed) - before(sum(billed))

)

And

if(isnull(

(sum(billed) - before(sum(billed))) / before(sum(billed))

),'0',

(sum(billed) - before(sum(billed))) / before(sum(billed))

)

Thanks for your new president , the french youngest president ever and may be in the world

regards