Exclude Value

[nihhalmca]

Hi All,

Ex: I have Year, Reseller name and amount fields.

Year         Reseller     Amount

2012           A              100

2012           B              101

2012           C              102

2013           C               200

2013           D               201

2014           D               300

2014           E               301

When will i click year: 2014 it has to show only Reseller Name: E (I need to exclude Reseller Names which has already been in previous years.

Regards,

Nihhal.

[MK_QSL]

Temp:

Load *, 1 as Flag Inline

[

  Year,         Reseller,     Amount

  2012,           A,              100

  2012,           B,              101

  2012,           C,              102

  2013,           C,               200

  2013,           D,               201

  2014,           D,               300

  2014,           E,               301

];

NoConcatenate

Final:

Load

  Reseller,

  Year,

  Amount,

  If(RowNo() = 1, 1, If(Reseller = Previous(Reseller),0,1)) as Flag

Resident Temp

Order By Reseller, Year;

Drop Table Temp;

Now use below as a List Box Expression or as a Text Box Expression

=Aggr(IF(Flag = '1', Reseller),Reseller,Year)

[maxgro · Accepted solution]

try with a chart

2 dimensions   

Year, Reseller

expression         

sum({<Reseller=P({1<Year={$(=max(Year))}>} Reseller)>-<Reseller=P({1<Year={$(=max(Year)-1)}>} Reseller)>} Amount)

[jagan]

Hi Nihhal,

Try like this

Dimensions: Year, Reseller

Expression: =Sum({<Reseller=E({1<Year={'<$(=Max(Year))'}>})>} Amount)

Hope this helps you.

Regards,

Jagan.

[nihhalmca]

Thank you its working perfect. Just i used variable for dynamic instead of max(year).

Regards,

Nihhal.

[nihhalmca]

Thank you Jagan Mohan. its working. Just i used variable for dynamic instead of max(year).

Regards,

Nihhal.