topic Re: How to: Recent seven days by the selected day in QlikView

How to: Recent seven days by the selected day

Wed, 23 Mar 2016 15:44:18 GMT

I need to show last seven days according to the selected day.

how to do it?

Thanks

Re: How to: Recent seven days by the selected day

Wed, 23 Mar 2016 16:38:16 GMT

Hi Ariel,

Can you post a sample QVW?

Re: How to: Recent seven days by the selected day

jepeerik — Wed, 23 Mar 2016 17:28:57 GMT

Take a look at this thread.

Set analysis sum between two given dates

There you'll find this Set Analysis example:

= sum({<OrderDate= {">=$(=min(StartDate))<=$(=max(EndDate))"}> } Netamountpaid)

If you change 'min(StartDate)' with 'max(EndDate)-7' you'll have a Set Analysis with a date range of 7 days. If you adjust the EndDate field with your own date-field (and off course the correct SUM field), then you'll have your 7 days according to your selection.

Re: How to: Recent seven days by the selected day

Anonymous — Wed, 23 Mar 2016 18:26:29 GMT

Maybe something like this :

sum({<date={">=$(=AddMonths(Max(date-6),0))<=$(=Max(date))"}>} Value).

I have pasted that from rolling seven days Set analysis a which a Google Search for "QlikView set analysis previous 7 days" found

Re: How to: Recent seven days by the selected day

santiago_respane — Wed, 23 Mar 2016 18:39:13 GMT

Hi Ariel,

There are a lot of ways to achieve that, it depends on what you need to do.

If you need a sum of values you can use set analysis:

=sum({<[Date field]={">=$(=v.SelectedValueMinusSeven)<=$(=v.SelectedValue)"}>} [Amount])

if you need to show dimention maybe using calculated dimension.

It would be great if you can share som data or elaborate more in order to help you finding the solution.

Kind regards,

Re: How to: Recent seven days by the selected day

maxgro — Wed, 23 Mar 2016 18:40:15 GMT

PFA

Re: How to: Recent seven days by the selected day

Wed, 23 Mar 2016 18:45:13 GMT

Thanks for the post MaxGro.

Re: How to: Recent seven days by the selected day

Thu, 24 Mar 2016 06:52:22 GMT

Hi,

Thanks for your help.

I think i need calculate dimension, i want to create fit X(date) by Y(sum) and control on the x-axis.

Thanks

Ariel

Re: How to: Recent seven days by the selected day

jagan — Thu, 24 Mar 2016 07:06:04 GMT

Hi,

Try this expression

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Max(Date)-6))<=$(=Date(Max(Date)))’}>} Sales )

Check this link for similar expressions for various scenarios

Set Analysis for Rolling Periods

Regards,

jagan.

Re: How to: Recent seven days by the selected day

Thu, 24 Mar 2016 07:11:51 GMT

Hi,

thanks.

Not understand the syntax. i have 2 column, sDate and sMoney and i want to create fit x(sDate) by y(sMoney).

Thanks

Ariel

Re: How to: Recent seven days by the selected day

Anonymous — Thu, 24 Mar 2016 07:50:48 GMT

Try like this

Date:

LOAD * INLINE [

Day, Value

01/01/2016, 10

02/01/2016, 20

03/01/2016, 30

04/01/2016, 40

05/01/2016, 50

06/01/2016, 60

07/01/2016, 70

08/01/2016, 80

09/01/2016, 90

10/01/2016, 10

];

Add a variable vMaxDate :=Date(Max(Date(Day))-7) /* MaxDate*/

Sum({<Day={"$(vMaxDate)"}>}Value)

Re: How to: Recent seven days by the selected day

Thu, 24 Mar 2016 08:11:26 GMT

Thanks, attached screen shots.

Please check, what i did wrong?

Re: How to: Recent seven days by the selected day

Kushal_Chawda — Thu, 24 Mar 2016 08:27:01 GMT

Select the Date you want and make sure that Date field is in proper Date format, use below expression

=sum({<Date ={">=$(=date(max(Date)-6))<=$(=max(Date))"}>}Amount)

Re: How to: Recent seven days by the selected day

Thu, 24 Mar 2016 08:39:02 GMT

Hi Kush141087,

thanks for your help.

I did what you are wrote, please see below. what i did wrong?

Thanks

Ariel

Re: How to: Recent seven days by the selected day

Anonymous — Thu, 24 Mar 2016 09:42:21 GMT

Add it in the Expression tab

Sum({<Day={"$(vMaxDate)"}>}Value).

Re: How to: Recent seven days by the selected day

Kushal_Chawda — Fri, 25 Mar 2016 05:50:09 GMT

What is the Dimension & Expression you are using? If date is dimension then try below expression

=sum(aggr(rangesum(above(total sum({<Date=>}Amount),0,7)),Date))

Re: How to: Recent seven days by the selected day

Sun, 27 Mar 2016 06:07:27 GMT

Hi,

This is my data:

TimeSelection:

LOAD * INLINE [

Period, dayS

Day, 1

Week, 7

Month, 30

Quarter, 91

HalfYear, 183

Year, 365

];

Time:

LOAD * INLINE [

Day, Value

01/01/2016, 10

02/01/2016, 20

03/01/2016, 30

04/01/2016, 40

05/01/2016, 50

06/01/2016, 60

07/01/2016, 70

08/01/2016, 80

09/01/2016, 90

10/01/2016, 10

];

I need to create Fit XY. x=value, y= Day.

I want to choose day and Period and i want to see in the graph the day and all the period before the day.

Example:

I want to see week before 09/01/2016:

Thanks

Ariel

Re: How to: Recent seven days by the selected day

tyagishaila — Mon, 28 Mar 2016 08:34:18 GMT

select Line chart than

Take Date as dimension

and

=sum({<Day={">=$(=Date(Max(Day)-6))<=$(=Date(Max(Day)))"}>} Money) as Expression