18 Replies Latest reply: Dec 27, 2016 4:11 AM by Hirish V

# Calculating previous week value

Hi All,

I have a weekend date as my filter option. As we follow different calendar so we have a different weekend dates.

Weekend date is the filter option.

I am calculating week on week where current week value is sum(sales)

the previous week formula that i have written is

SUM({<WEEKEND_DATE = {'\$(=MAX(WEEKEND_DATE-7))'}>} sales)

but after many try and attempts of doing this i am getting the value as 0 always. Please suggest.

Note : Weekend date is difference of 7 days

• ###### Re: Calculating previous week value

Hi,

Try this,

using your max(Date) and modify your'e date format,

```Sum({\$<Date={">=\$(=Date(WeekStart(Max(Date),-1, 0),'DD-MM-YYYY'))<=\$(=Date(WeekEnd(Max(Date),-1, 0),'DD-MM-YYYY'))"}>}Sales)
```

HTH,

Hirish

• ###### Re: Calculating previous week value

Hi Hirish,

I already have week start date with me.. Just want to know the previous week value.

• ###### Re: Calculating previous week value

Hi,

Check this

```Sum({\$<WEEKEND_DATE={"\$(=Weekend(Max(WEEKEND_DATE),-1))"}>}sales)

```

If it doesn't work means, There may be no data for the previous week  or some date format issue may persist with it.

Else,please post some sample data.For further assistance.

HTH,

-Hirish

• ###### Re: Calculating previous week value

SUM({<WEEKEND_DATE = {"\$(=DATE(MAX(WEEKEND_DATE-7),'YourDateFormatforWeekendDate')"}>} sales)

• ###### Re: Calculating previous week value

Hi Vineeth,

No Luck

Its still Null.

• ###### Re: Calculating previous week value

try

SUM({<WEEKEND_DATE = {"\$(=DATE(MAX(WEEKEND_DATE)-7,'YourDateFormatforWeekendDate')"}>} sales)

• ###### Re: Calculating previous week value

Hi All,

No Luck.

I tried with most of the date formats but its not working.. if i am fetching the value for filtered weekend date its displaying correctly but by the time i add " - 7 " to it.. the value becomes zero.

• ###### Re: Calculating previous week value

Hi,

I managed to get the query right, but another problem came up

SUM({<WEEKEND_DATE = {'\$(=MAX(WEEKEND_DATE))'}>} sales)

- SUM({<WEEKEND_DATE = {'\$(=MAX(DATE(DATE(WEEKEND_DATE - 7,'DD/MM/YYYY'),'DD-MM-YYYY')))'}>} sales)

I am subtracting the selected weekend sales - previous weekend sales

but rather than that it displaying me the values in 2 separate lines.

• ###### Re: Calculating previous week value

Hi,

Do like this i think you will get.

First create on variable like

vTT=date(date(max(DT1),'DD/MM/YYYY')-7,'DD/MM/YYYY')  --> variable overview

after write below expr

=sum({<DT1={"\$(vTT)"}>}Marks)

• ###### Re: Calculating previous week value

Hi Lakshmipathi,

Its still the same. Its displaying as 0. some where i feel that there is a problem with the date that we are using.

• ###### Re: Calculating previous week value

Hi,

It is working my side. Post your wkdate field values format.

• ###### Re: Calculating previous week value

my weekend date format in the oracle table is like "07-JAN-16"

When I am using that in Qlik sense - Filter Pane, I am converting that into

DATE((WEEKEND_DATE),'DD-MM-YYYY')

Now in the filter pane, I can see the values as " 15-12-2016"

• ###### Re: Calculating previous week value

Hi,

Then create variable like this

vTT=date(date(date#(max(DT1),'DD-MMM-YY'),'DD/MM/YYYY')-7,'DD/MM/YYYY')

• ###### Re: Calculating previous week value

Sorry tried but did not work..

• ###### Re: Calculating previous week value

Hi Ishan,

SUM({<WEEKEND_DATE = {'\$(=MAX(WEEKEND_DATE-7))'}>} sales). Will work if an already weekend is selected in the App. So please use below expression

SUM({<WEEKEND_DATE = {'\$(=MAX({1}WEEKEND_DATE-7))'}>} sales). It will by default take the max weekend_date and retrive the value -7.

• ###### Re: Calculating previous week value

Hi Vj,

The problem is when - 7 days is included in the query its not working but if i remove that -7 it is giving the current week information correctly but last week is not.

• ###### Re: Calculating previous week value

Hi,

Pls provide some sample data and mention which query your'e using in script.

Did u try this,from my above post.

1. Sum({\$<WEEKEND_DATE={"\$(=Weekend(Max(WEEKEND_DATE),-1))"}>}sales)

-Hirish

• ###### Re: Calculating previous week value

SUM({<WEEKEND_DATE = {'\$(=MAX({1}WEEKEND_DATE)-7)'}>} sales). Sorry have corrected the code. Replaced MAX({1}WEEKEND_DATE-7) with MAX({1}WEEKEND_DATE)-7.