Set analysis complicated

[zied_ahmed1]

Hello,

I will explain more the requirement :

I have 3 fields month,Cate and sales

If I select June i will have the sum Sales of A16-06 (06 is the June ) and 05 (Mai) and 04 (April) of the field CAT ,if we have 03 02 01 i need to do sum like others too

Thanks for help

[sibin_jacob]

Create two more Column in the Category table.

SubField(Category,'-',1) as Category1,

SubField(Category,'-',2) as Category2

Then after loading the data, createa variable for the Month value.

variable name:

VMonth

Definition

=Date(Date#(Month_Vaue,'MMM'),'MM')

Then set analysis write like below.

=Sum({<Category2 = {"<=$(VMonth)"}> } Sales)

IF you need sum only the values starting with A16, use the below expression

=Sum({<Category2 = {"<=$(VMonth)"},Category1={'A16'}> } Sales)

If you want exclude the Month Column selection.

=Sum({<Category2 = {"<=$(VMonth)"},Month_Column=> } Sales)

[zied_ahmed1]

Thank you for your answer:

I have two problem with your solution :

Month :

Load * inline [

Month, month

01, january

02, february

03, mars

04, april

05, may

06, june

07, july

08, august

09, september

10, october

11, november

12, december

];

1- I user load inline to create the month and it is not associated

2- When i test the variable with a kpi object i have -

[sibin_jacob · Accepted solution]

if you already created inline table, you can use the variable definition like below

variable name:

VMonth

Definition

=max(MonthID)

it will be good, if you keep the Month column as MonthID

[zied_ahmed1]

I think it works..just one question...why we use max MonthId and not directly Month that's give the same thing no? ..

[sibin_jacob]

if you use Max MonthId, it will pick latest month, if month is not selected.

if you use Direct MonthID, it has multiple values and  nothing is selected, it will show blank "-"