Need help

[paulwalker]

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

[Clever_Anjos]

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

QlikCommunity Tip: How to get answers to your post?

[krishna_2644]

This way:

[paulwalker]

Please can you share expression

[krishna_2644]

=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.

[#6]

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)

[MarcoWedel]

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

[hic]

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