Weekly Data Comparison

[shah_sapana]

I have one date from each week and data for each date. Date can vary and can not be calculated by adding or subtracting fix number.

I need to show current week vs previous week data. 

I created filter for date column. Need help calculating data for previous week by what ever available date.

I am trying to achieve it by giving sequence number to dates. But not able to find right set analysis. 

Here is sample code :

AutoNumber(T_Date) As T_DateSeq,
If(AutoNumber(T_Date)=1, AutoNumber(T_Date), AutoNumber(T_Date)-1) As T_PreDateSeq,

Filter Dimension : T_Date

Current Balance :  Sum ({T_DateSeq}Current_Balance) )

Previous Balance : Sum ({T_PreDateSeq}Current_Balance) )

I have attached data sample and dashboard visual for reference. 

Any suggestion would be really appreciated.

[sasiparupudi1]

Try using weekstart function in set analysis to get the last week's amount

Sum({<T_Date={">=$(=Date(weekstart(T_Date)-7))<(=weekstart(T_Date))"}>} Amount)

Or please post a sample

Ps: You can try debugging the expression using a KPI object

[sunny_talwar]

Are you going to be using T_Date as a dimension in the chart where you are looking to view this?

[shah_sapana]

That's correct ! T_Date would be dimension in the chart.

To add more detail to help you understand, there is going to be one more dimension "Property"  to narrow down result at Property level.

Please advise.

[sunny_talwar]

As I suggested in my private message, you need to look into The As-Of Table

[shah_sapana]

Thanks !

But Sum({<T_Date={">=$(=Date(weekstart(T_Date)-7))<(=weekstart(T_Date))"}>} Amount doesn't work with this data set as T_Date would not be fixed all the time. For previous week it might be T_Date-7 or T_Date-5 or any other number.

Sample is already attached. Please suggest.

[shah_sapana]

Thanks for your time and valuable suggestions !

I used set analysis using Date Sequence.

sum({<Date=,T_DateSeq=p(T_PreDateSeq)>} [Current Balance])

It worked as per my requirement. I was just missing one part since beginning. In order to make Previous Week's Date Sequence work, I need to remove link between T_Date column and Previous Week's Balance column. 

Above set analysis did that wonder !

Sharing as it might help others looking for solution !