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: 
Not applicable

Need help with showing differences

I want to show the difference between 2012 and 2011 data.  I have the data in the pivottable chart in Qlikview, but I cannot figure out how to show the difference between year.  For example, i sold 10000 of product A in 2011, and 12500 of product A in 2012.  I want another column that shows me the difference of 2500.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

You can do this with the following expressions (modify as required):

  • Sum({<Year = {2011}>} Sales)
  • Sum({<Year = {2012}>} Sales)
  • Sum({<Year = {2012}>} Sales) - Sum({<Year = {2011}>} Sales)

    

You need a Year field for this to work. If you don't have one, add one in your load script with

     LOAD

          Sales,

          SalesDate,

          Year(SalesDate) As Year,

          ..

     FROM ..;

You might want to post your qvw (or a sample thereof) for more specific assistance.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

7 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

You can do this with the following expressions (modify as required):

  • Sum({<Year = {2011}>} Sales)
  • Sum({<Year = {2012}>} Sales)
  • Sum({<Year = {2012}>} Sales) - Sum({<Year = {2011}>} Sales)

    

You need a Year field for this to work. If you don't have one, add one in your load script with

     LOAD

          Sales,

          SalesDate,

          Year(SalesDate) As Year,

          ..

     FROM ..;

You might want to post your qvw (or a sample thereof) for more specific assistance.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
adhudson
Creator II
Creator II

Hi,

     I should agree with Jonathan. His solution will work perfectly.

     But you can also try this expression which is exactly what you have asked.

     Count({<Product={'A'}, Year={2012}>} DISTINCT Product) - Count({<Product={'A'}, Year={2011}>} DISTINCT Product)

     The above expression gives you the difference of no of Product A sold in 2012 and 2011.

Regards

Andrew Hudson

jagan
Luminary Alumni
Luminary Alumni

Hi,

No need of writing any new expressions for this, just use the expressions you already used for 2011 and 2012. 

Suppose if your expression name Year 2011 and Year 2011 the the difference of this expression would be

=[Year 2012] - [Year 2011]

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

Thanks to all for this help, I really appreciate it!

v_iyyappan
Specialist
Specialist

Hi,

           Close this discussion by clicking Correct and Help answers for the posts which really helped

you, so others will find the solution easily.

Regards,

Iyyappan

Not applicable
Author

One more question, I want to see the difference between year and a specific company.  So i need to say where year = 2011, which is work, but also where COMPANY = KateAspen.  I need that in the same query, how would i do that.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Just add it to the set expression like this:

Sum({<Year = {2011}, Company = {'KateAspen'}>} Sales)

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein