textbox termy sales

[Anonymous]

hi there,

ı have a table like that ;

term	sales
201711	90
201710	90
201709	91
201708	90
201707	89
201706	88
201705	86
201704	85
201703	84
201702	82
201701	81
201612	80
201611	79
201610	79
201609	77
201608	77
201607	75
201606	76
201605	73
201604	74
201603	72
201602	71
201601	70
201512	70
201511	69
201510	68
201509	66
201508	66

I want to write in the textbox  that ;

"the sales in the month 201512 are 70"

201512 is (current month- 24 months) (moving)

thanks for helps.

[sunny_talwar · Accepted solution]

May be this

'the sales in the month ' & Max({<term = {"=term = Num#(Date(MonthStart(Today(), 1), 'YYYYMM')) - 200"}>}term) & ' are ' & Sum({<term = {"=term = Num#(Date(MonthStart(Today(), 1), 'YYYYMM')) - 200"}>} sales)

[Anonymous]

thnks my friend,

then what if I want to add a second dimension like that, I could not fix the set analysis;

term	flag1	sales
201711	a	90
201710	a	90
201709	a	91
201708	a	90
201707	a	89
201706	b	88
201705	b	86
201704	b	85
201703	b	84
201702	b	82
201701	b	81
201612	a	80
201611	a	79
201610	a	79
201609	a	77
201608	a	77

I want to call the sales filtered by flag1 (also all other flags (listboxes etc.) should be excluded, do not influence the sum)

I need an adjustment in set analysis I think,

thanks for your helps

[sunny_talwar]

Not sure I understand... what is the expected output from this new sample data?

[Anonymous]

sorry for the sample, was not clear,

I revised ;

term	flag1	sales
201711	a	90
201710	a	90
201710	b	91
201709	a	90
201708	a	89
201707	b	88
201706	b	86

there are 2 of 201710 , a and b

I want to take the one flag1=a

"the sales in the month 201710 and a (flag1) are 90"

in brief I want to add a filter to the set analysis that you sent.

[sunny_talwar]

How do you decide which term do you want to see? Is this random or is there a logic behind this?

[Anonymous]

ı just want to use multiple filters other than only term, ı want to add other filters to set expression

[sunny_talwar]

Something like this

'the sales in the month ' & Max({<term = {"=term = Num#(Date(MonthStart(Today(), 1), 'YYYYMM')) - 200"}, flag1 = {'a'}>}term) & ' are ' & Sum({<term = {"=term = Num#(Date(MonthStart(Today(), 1), 'YYYYMM')) - 200"}, flag1 = {'a'}>} sales)

[Anonymous]

thats it, thanks!

[Anonymous]

than I also want to write in textbox someth like;

'in the last (12) months of term, rank=1 and rank=2 sum(sales) are .....  and in these months..... '

again I am having difficulty with the syntaxes of set analysis.............