How to get the sales of last period?

[Anonymous]

Fact_Sales:
LOAD * INLINE [
Period,Sales
201101,500
201103,750
201104,650
201104,120
201105,900
...
];

Dim_Period:
LOAD * INLINE [
Period_ID,Period
1,201101
2,201102
3,201103
4,201104
5,201105
...
]

I want to get straight table as following

Period	Sales	Sales_LP
201101	500	0
201102	0	500
201103	750	0
201104	770	750
201105	900	770
...	...	...

column Sales = sum(Sales)

column Sales_LP = ?

[kaushiknsolanki]

Hi,

    Create a chart (Straight table) with dimension as period and two expression as

    1. sum(Sales)   - Give label as Sales

    2. above([Sales])  - Give label Sales_LP

Regards,

Kaushik Solanki 

[Anonymous]

Dear Kaushik Solanki, sorry for my expression.

I want to achieve it by using Set Analysis ,because even if I select just only 201104, I also want to get Sales_LP of it.

[kaushiknsolanki]

Hi,

    Following expression can be used, but there will be no effect of selection on chart.

    1. sum({1}Sales)  - Give label as Sales

    2. above([Sales])  - Give label Sales_LP

Regards,

Kaushik Solanki

[its_anandrjs]

Hi,

You need to put the expressions for 

Sales_LP =Above(Sum( Sales)) and for

Sales =Sum(Sales)

and check the results.

Regards,

Anand

[sbaldwin]

Hi, I have been trying to solve a similar problem, and i don't think you can do it IF you want to keep the period as a dimension as it is against this dimension that the set analysis is trying to be done against, the way i solved this is to have two tables, the first as described in the previous posts and a second that has no dimensions and 3 expressions, these being:

only(Period)
sum({<Period_ID ={$(=only(Period_ID))}>} Sales)
sum({1<Period_ID ={$(=only(Period_ID)-1)}>} Sales)

then do conditional show and hides based on the count of the period so when you select a period it will hop to this second table.

Of course you could just add another column in your table called preivous_sales

Thanks

Steve

[Anonymous]

Dear sbaldwin,

     Yes, I want to keep the period as a dimension, is it impossibility by using Set Analysis?

[sbaldwin]

Hi, i think it is as i have spent quite a bit of time trying! pls post back if you can figure it out.

Thanks

Steve