Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try
sum({$ <PeriodSequence={'>$(V1)<$(V2)'}>} sales)
where V1 and V2 are two variables with values you want
Hi
Create two variables
var1=MinString(PeriodSequence);
Var2=MaxString(PeriodSequence);
sum({<PeriodSequence= {'<=$(=Var1) >$(=Var2)'}> } sales)
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
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
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&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
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.
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
Hi,
Try this expression.
sum({<PeriodSequence= {'<=$(=PeriodSequence) >$(=PeriodSequence-13)'} , Period = > } sales)
Regards,
Kaushik Solanki
Hi Kaushik,
Thank you for your response..I tried that but in vain..
Regards
Ravi