Qlik Sense - calculating difference and difference percentage

[cseward1963]

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

[brunobertels]

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

[cseward1963]

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

[brunobertels]

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

[cseward1963]

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

[cseward1963]

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

[brunobertels]

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

[cseward1963]

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

[brunobertels]

Hi Celia

I'm glad to help

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