How to get previous week value in current week ? Week is the horizontal dimension in Pivot table

[Rajkumar]

Hi Everyone,

I have a requirement to get a previous week value in current week  to calculate growth % and  week as horizontal dimension.

I have used the after function to achieve this but when I select 2 weeks ,previous week will not show growth  % because after function will not get the previous value .

Please suggest to achieve this.

[Mike_Dickson]

Hello @Rajkumar 

Using Qlik Sense to achieve the goal of getting the previous week's value in the current week can be accomplished using a combination of functions and expressions. Given your problem statement, it looks like you're facing an issue when only two weeks are selected, as the After() function may not work as expected.

To calculate the growth percentage from the previous week, follow these steps:

1. Set Analysis Approach: Using set analysis, you can determine the previous week's value. Here's how:

   1.1. Create a new measure for the previous week's value:

   Sum({<Week = {"$(=Max(Week)-1)"}>} Value)
   

   Here, Max(Week)-1 calculates the week number of the previous week. Make sure to adjust the field names based on your dataset.

   1.2. Now, calculate the growth percentage:

   (Sum(Value) - Sum({<Week = {"$(=Max(Week)-1)"}>} Value)) / Sum({<Week = {"$(=Max(Week)-1)"}>} Value) * 100
   

2. Above() Function Approach: Instead of the After() function, you can also use the Above() function to get the previous week's value directly within the pivot table:

   Sum(Value) - Above(Sum(Value))
   

   Then for the growth percentage:

   (Sum(Value) - Above(Sum(Value))) / Above(Sum(Value)) * 100
   

3. Considerations: Ensure that your week dimension is sorted in ascending order so that the functions can correctly identify the "previous" week.

4. Alternate States: If you frequently need to compare different data states in your Qlik application, you might want to consider using "Alternate States." This powerful feature allows you to assign different selections to different visualizations or measures within the same sheet, enabling more direct comparisons.

5. Limitations: The solution might require some tweaking based on your actual data model, dimension names, and other specific requirements or conditions in your app.

Always test these approaches in a sandbox or backup environment first to ensure accuracy and that it doesn't disrupt any other functionality.