Calculate Previous completed week total sales

[qvqs]

I am using the following dimensions to calculate previous week sales in Pivot table. Also by default we have WeekEnd_Date trigger set to max Week EndDate. Based on the selection it should change.

Variable is already defined : =Date(max (WE_Date)-7,'MM/DD/YY')

Dimesnion 1: =ValueList('Prior Week Sales','Prior Week Cases','Prior Week Case Cost')
prior week sales dimension 2
=ValueList(
Date(Date#('$(vPreviousWeekEnd)', 'MM/DD/YY') - 6, 'MM/DD/YY'), // Sunday
Date(Date#('$(vPreviousWeekEnd)', 'MM/DD/YY') - 5, 'MM/DD/YY'), // Monday
Date(Date#('$(vPreviousWeekEnd)', 'MM/DD/YY') - 4, 'MM/DD/YY'), // Tuesday
Date(Date#('$(vPreviousWeekEnd)', 'MM/DD/YY') - 3, 'MM/DD/YY'), // Wednesday
Date(Date#('$(vPreviousWeekEnd)', 'MM/DD/YY') - 2, 'MM/DD/YY'), // Thursday
Date(Date#('$(vPreviousWeekEnd)', 'MM/DD/YY') - 1, 'MM/DD/YY'), // Friday
Date(Date#('$(vPreviousWeekEnd)', 'MM/DD/YY'), 'MM/DD/YY') // Saturday
)

Expression :

Pick(
Match(
ValueList(
'Prior Week Sales','Prior Week Products','Prior Week Product Cost'
),
'Prior Week Sales','Prior Week Products','Prior Week Product Cost'

),
Sum(sales_amount), Sum(Product_Cost), Sum( sales_amount) /Alt(Sum( Product_Cost), 0))

But, Getting the same values for the entire dates. Images attached

[marcus_sommer · Accepted solution]

Your aggregations have no relations to the dates - which are just a plotted free-text within the table.

Even if it's technically possible to create calculated dimensions in the UI - it shouldn't be done unless there is no other way. Else recommended is to move the logic or at least the essential parts into the data-model.

In your case there are various ways to use native fields to define the target. The most simple one would be just to use the weekday from the calendar as dimension and to add the condition to the previous week within the expression in a set analysis, maybe like:

sum({< RunningWeek = {"$(=max(RunningWeek)-1)"}>} Field)

Nearly all thinkable information about any period-relationship are applicable within a calendar and by using The As-Of Table - Qlik Community - 1466130 all kinds of overlapping logic became quite easy to implement.