topic Re: Set analysis complicated in Management & Governance

Set analysis complicated

zied_ahmed1 — Mon, 13 Aug 2018 10:03:26 GMT

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

Re: Set analysis complicated

sibin_jacob — Mon, 13 Aug 2018 11:01:19 GMT

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)

Re: Set analysis complicated

zied_ahmed1 — Mon, 13 Aug 2018 11:56:16 GMT

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 -

Re: Set analysis complicated

sibin_jacob — Mon, 13 Aug 2018 12:06:26 GMT

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

Re: Set analysis complicated

zied_ahmed1 — Mon, 13 Aug 2018 12:17:16 GMT

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

Re: Set analysis complicated

sibin_jacob — Mon, 13 Aug 2018 12:23:38 GMT

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 "-"