topic Re: Calculating previous week value in QlikView

Calculating previous week value

ishanbansal1204 — Sun, 25 Dec 2016 13:07:34 GMT

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

HirisH_V7 — Sun, 25 Dec 2016 13:26:49 GMT

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

vinieme12 — Sun, 25 Dec 2016 15:33:29 GMT

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

Re: Calculating previous week value

ishanbansal1204 — Mon, 26 Dec 2016 04:01:49 GMT

Hi Vineeth,

No Luck

Its still Null.

Re: Calculating previous week value

ishanbansal1204 — Mon, 26 Dec 2016 04:02:44 GMT

Hi Hirish,

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

Re: Calculating previous week value

HirisH_V7 — Mon, 26 Dec 2016 04:45:49 GMT

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

pathiqvd — Mon, 26 Dec 2016 05:06:34 GMT

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

ishanbansal1204 — Mon, 26 Dec 2016 06:28:40 GMT

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

pathiqvd — Mon, 26 Dec 2016 06:38:44 GMT

Hi,

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

Re: Calculating previous week value

ishanbansal1204 — Mon, 26 Dec 2016 06:43:36 GMT

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

pathiqvd — Mon, 26 Dec 2016 06:54:46 GMT

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

ishanbansal1204 — Mon, 26 Dec 2016 08:41:39 GMT

Sorry tried but did not work..

Re: Calculating previous week value

parimikittu — Mon, 26 Dec 2016 08:48:09 GMT

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

ishanbansal1204 — Mon, 26 Dec 2016 09:06:50 GMT

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

parimikittu — Mon, 26 Dec 2016 09:09:55 GMT

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.

Re: Calculating previous week value

vinieme12 — Mon, 26 Dec 2016 11:42:50 GMT

try

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

Re: Calculating previous week value

ishanbansal1204 — Tue, 27 Dec 2016 04:17:20 GMT

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

ishanbansal1204 — Tue, 27 Dec 2016 08:04:59 GMT

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.

Can you please suggest

Re: Calculating previous week value

HirisH_V7 — Tue, 27 Dec 2016 09:11:59 GMT

Hi,

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

Did u try this,from my above post.

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

-Hirish