set analysis problem

[#1]

I m having this expression in my chart column for last year sales

Num((SUM({<year = {'$(=Max(year)-1)'}>}saleQty)),'#,##0')

now i want to add another sale from excel sheet. excel sheet is having these columns

year	month	targetQty	targetVal	saleQty	saleVal	location	channel
2013	1	105787	74703800	97765	74168820.00	SRI	WholeSale
2013	2	107388	78866450	94307	73088355.00	SRI	WholeSale
2013	3	126763	91652950	124057	102011660.00	SRI	WholeSale
2013	4	89166	68911105	96979	78904750.00	SRI	WholeSale
2013	5	116636	90696277	118874	95489510.00	SRI	WholeSale
2013	6	103786	81375835	101095	79750905.00	SRI	WholeSale
2013	7	100659	76873217	97846	80109140.00	SRI	WholeSale
2013	8	100543	77188747	101709	80537405.00	SRI	WholeSale
2013	9	113345	86771692	109252	85841875.00	SRI	WholeSale
2013	10	108881	85664090	109193	82151115.00	SRI	WholeSale
2013	11	100599	79617185	99262	79019855.00	SRI	WholeSale
2013	12	95143	75442820	90344	71745635.00	SRI	WholeSale

i want to add this saleQty to above expression. how can i do this

[jagan · Accepted solution]

HI,

Concatenate this data to your existing table like this

Concatenate(ExistingTableName

LOAD

*

FROM Datasource;

Now use the same expression.

Regards,

Jagan.

[kumarnatarajan]

HI Dushan,

I think your set analysis is right.

EX:

Num((SUM({$<year = {"$(=Max(year)-1)"}>}saleQty)),'#,##0')

First check your year field format. Then also check

=Max(year)-1 in text box what it has been return then you will get good ideas.

[#4]

Hi,

As per your post you want to add another sales column against the dimension as you are using now in the chart.

This is your current expression for salesQty

Num((SUM({<year = {'$(=Max(year)-1)'}>}saleQty)),'#,##0')

If you want to calculate the sales on another column like SalesVal

Num((SUM({<year = {'$(=Max(year)-1)'}>}SalesVal)),'#,##0')

Now you will get total sales by adding these 2 expressions.Now your final expression would be like

Num((SUM({<year = {'$(=Max(year)-1)'}>}saleQty)),'#,##0') + Num((SUM({<year = {'$(=Max(year)-1)'}>}SalesVal)),'#,##0')

If this is not you are looking for then eloberate your requirement in detailed.

Regards

Kumar

[#5]

thanks all