Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
shah_sapana
New Contributor

Weekly Data Comparison

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.

 

 

 

 

6 Replies
Highlighted
sasiparupudi1
Honored Contributor III

Re: Weekly Data Comparison

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

Highlighted

Re: Weekly Data Comparison

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

Highlighted
shah_sapana
New Contributor

Re: Weekly Data Comparison

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.

 

Highlighted

Re: Weekly Data Comparison

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

Highlighted
shah_sapana
New Contributor

Re: Weekly Data Comparison

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.

 

 

Highlighted
shah_sapana
New Contributor

Re: Weekly Data Comparison

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 !