topic Need help with showing differences in QlikView

Need help with showing differences

Thu, 19 Apr 2012 18:05:32 GMT

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.

Need help with showing differences

jonathandienst — Thu, 19 Apr 2012 18:32:57 GMT

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

Need help with showing differences

adhudson — Fri, 20 Apr 2012 00:38:52 GMT

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

Re: Need help with showing differences

jagan — Fri, 20 Apr 2012 06:08:20 GMT

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.

Re: Need help with showing differences

Fri, 20 Apr 2012 12:04:10 GMT

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

Re: Need help with showing differences

v_iyyappan — Fri, 20 Apr 2012 15:00:25 GMT

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

Need help with showing differences

Fri, 11 May 2012 14:31:45 GMT

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.

Need help with showing differences

jonathandienst — Sat, 12 May 2012 13:32:56 GMT

Just add it to the set expression like this:

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

Regards

Jonathan