topic Re: Set analysis - sum condition in QlikView

Set analysis - sum condition

Tue, 26 Mar 2013 08:44:29 GMT

How to count the number of licences in newest version?

There are have two tables(below): Versions and Licences of application

Version table

Version.Key	VersionDateIn	VersionDateOut
1	2009-10-01	2010-03-03
2	2010-03-04	2011-12-04
3	2011-12-05	2012-05-23

Licence table

Licence	Version.Key	SaleDate	SaleDateYM
ABC45	1	2009-11-02	2009-11
ABC23	1	2009-11-25	2009-11
ABC65	1	2010-01-14	2010-01
ABC76	2	2010-05-12	2010-05
ABC99	2	2011-08-23	2011-08
ABC43	3	2012-01-12	2012-01

How to obtain the column 3 : Sum all newest licences aggregated in period

SaleDateYM	Sum of sold in YM	Sum all licences in newest version
2009-11	2	2 (current ver.key 1)
2010-01	1	3 (current ver.key 1)
2010-05	1	1 (current ver.key 2)
2011-08	1	2 (current ver.key 2)
2012-01	1	1 (current ver.key 3)

Sum of sold is very easy to count but how to obtain sum all licences in newest version in period ?

Re: Set analysis - sum condition

Tue, 26 Mar 2013 09:56:47 GMT

Your expression is for column(2) . Column(3) isn't standard aggregation

Re: Set analysis - sum condition

Tue, 26 Mar 2013 10:53:58 GMT

Hi,

Try using RangeSum function for 3rd column

Best of luck

Re: Set analysis - sum condition

Tue, 26 Mar 2013 11:42:36 GMT

Hi ,

Attached app is having your output but i'm not sure this approach is perfect way to achieve.

Finally it is having the result as you expected.

Best of luck

Re: Set analysis - sum condition

parthiband — Wed, 27 Mar 2013 06:42:37 GMT

Hey see am not sure about the answer which i got will suits u or not..

But i got your desired results

The following code worked for me

if(V_Key=1,Rangesum(Above(Total Count({$<V_Key={'1'}>}V_Key),0,2)),if(V_Key=2,Rangesum(Above(Total Count({$<V_Key={'2'}>}V_Key),0,2)),Rangesum(Above(Total Count({$<V_Key={'3'}>}V_Key),0,2))))

I'd attached the solution below!!

All the best..