topic Re: Need help in QlikView

Need help

paulwalker — Wed, 04 Nov 2015 16:35:06 GMT

Hi Community,

I have Year and Month selections..

One requirement... If i am click year(210), and Month(March)

Table:

Category	Date	Cost
Men's Clothes	01/19/2010	1,051
Men's Clothes	02/12/2010	1,178
Men's Clothes	02/09/2010	1,281
Men's Clothes	03/24/2010	1,660
Men's Clothes	04/21/2010	2,004

I want to sum up data 2010 wise, Month(Jan, Feb and March)

Depends on Current selections

My output should be

Category Cost

Men's Cloths 5170 (1051+1178+1281+1660)

If i am click Fed Month

Category Cost

Men's Cloths 3510 (1051+1178+1281)

if i am click Nov, i want sum up data Jan to Nov

Thanks in Advance

Re: Need help

Clever_Anjos — Wed, 04 Nov 2015 16:36:29 GMT

It would be easier if you post some data or a sample of your app

QlikCommunity Tip: How to get answers to your post?

Re: Need help

Qrishna — Wed, 04 Nov 2015 17:19:48 GMT

This way:

Re: Need help

paulwalker — Wed, 04 Nov 2015 17:22:40 GMT

Please can you share expression

Re: Need help

Qrishna — Wed, 04 Nov 2015 17:24:46 GMT

=Pick(match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),

sum({$<Month = {'Jan'}>}Cost),

sum({$<Month = {'Jan','Feb'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr','May'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr','May','Jun'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr','May','Jun','Jul'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov'}>}Cost),

sum({$<Month = {'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'}>}Cost)

)

This expression applies to text box.

In case you need for charts,this wont work i guess.

Re: Need help

Wed, 04 Nov 2015 17:36:41 GMT

I loaded month as numeric value 1 for Jan,2 for Feb

test:
LOAD * Inline [
Category, Year, Month, Cost
Men's Clothes, 2010, 1, 1051
Men's Clothes, 2010, 2, 1178
Men's Clothes, 2010, 2, 1281
Men's Clothes, 2010, 3, 1660
Men's Clothes, 2010, 4, 2004
];

and used this expression and it's working as you want.

=sum({<Month={'<=$(=Max(Month))'}>}Cost)

Re: Need help

MarcoWedel — Wed, 04 Nov 2015 22:21:49 GMT

Hi,

another solution could be an As-Of Table like described by hic in this post:The As-Of Table

(The additional table maybe only makes sense if you have other requirements that can be covered with. The solution proposed by QVDTEST1 might be more efficient in your case.)

hope this helps

regards

Marco

Re: Need help

hic — Thu, 05 Nov 2015 16:33:34 GMT

The As-Of table is one solution, just like MarcoWedel suggests.

A second solution is to use the Above function in a chart with Month as dimension, with the following expression

Only(Aggr(RangeSum(Above(Sum({$<Month=>} Cost),0,12)), Month)

See The Above Function

HIC