Issue in set analysis

[#1]

Dear All,

I am having the fields year( data is like '2011' etc.), period (P01,P02...P12), Period sequence (1,2,3..12),sales coming to the dashboard and a filter is set on period and year in the dashboard.

When a period is selected from this filter, I need to calculate on the sum of a sales for the last 13 periods.

I am using the set analysis on periodsequence, but it is not working fine.

sum({<PeriodSequence= {'<=$(=PeriodSequence) >$(=PeriodSequence-13)'}> } sales)

So I tried, creating a variable at the dashboard level ( Not at script level) and assigned period sequnce value to it and then used this variable in the set analysis, but this is too not working .

sum({<PeriodSequence= {'<=$(=Var) >$(=Var-13)'}> } sales)

And also I 've noticed a difference in another context of using variables created at script level and dashboard level while using in set analysis. Is it right ??

Could some one suggest a way to sort this out ?? Thank you.

Regards

Ravi

[alexandros17]

try

sum({$ <PeriodSequence={'>$(V1)<$(V2)'}>} sales)

where V1 and V2 are two variables with values you want

[sreenivas]

Hi

Create two variables

var1=MinString(PeriodSequence);

Var2=MaxString(PeriodSequence);

sum({<PeriodSequence= {'<=$(=Var1) >$(=Var2)'}> } sales)

[nilesh_gangurde]

Hii,

Create the Date Field first at load time.

makedate(year,period,01) as DATE

then

Put the Following expression.

sum({<DATE = {'<=$(=Var1) >$(=Var2)'}> } sales)

and create the variable by puting values as follows:

for Var1 : "=Date(GetFieldSelections( DATE))";

and for var2:   "=DAte(GetFieldSelections(DATE))"

Hope this will help...

-Nilesh

[#5]

Hi Ravi,

I think your front-end expression should read like this

sum({<PeriodSequence= {'>=$(Var-13)<=$(Var)'}> } sales)

There is no need for the = before the variable if you have this before the dollar-sign expression. i also re-ordered your variables

Regarding the anomylie at script level; My initial thought as to why would be your dimension at which the value was evaluated at.

Let me know if you come right with this mate

Cheers,

Byron

[sridhar240784]

It would be very easy when you covert your year and period field into date field and use that custom defined field in set analysis, your custom date field should some thing like below .

Syntax to create custom date field :

Date(Date#(year&amp;purgechar(period,'P'),YYYYMM)) as datefield,

Set analysis Syntel would be:

Sum({<datefield ={">=$(date(addmonths(max(datefield),-12)))<=$(=date(max(datefield)))"}>}

Have not tested the above expression, your expression should be something like above.

Hope this helps you.

-Sridhar

[jagan]

Hi Ravi,

In script arrive a new field called PeriodNumber which has numeric values for period, and then use the following expression

LOAD

     *,

     Num(Purgechar(PeriodSequence, 'P')) AS PeriodSequence

FROM DataSource;

Now use this field in the expression like this
=sum({<PeriodSequence=, PeriodNumber= {'>$(=Max(PeriodNumber) - 13) <=$(=Max(PeriodNumber))'}> } sales)

Hope this helps you.

Regards,

Jagan.

[#8]

Hi Jagan,

Thank you for your response. But I've already a field named period sequence(1,2,3,..) which is a number. I tried set analysis with this field only

sum({<PeriodSequence= {'<=$(=PeriodSequence) >$(=PeriodSequence-13)'}> } sales)  but in vain.

Then only I've created a variable(Var) at the dashboard level and assigned value of period sequence  and then used this variable in the set analysis

but this is also not working..

With this information, Could you suggest some solution ??? One more thing, Can we use a mapping created at script level in  applymap() at the dashboard level calculation ??

Thank you

Regards

Ravi

[kaushiknsolanki]

Hi,

     Try this expression.

     sum({<PeriodSequence= {'<=$(=PeriodSequence) >$(=PeriodSequence-13)'} , Period = > } sales)

Regards,

Kaushik Solanki    

[#10]

Hi Kaushik,

Thank you for your response..I tried that but in vain..

Regards

Ravi