topic Set Analysis For calculating Last 4 weeks total in QlikView

Set Analysis For calculating Last 4 weeks total

Fri, 21 Nov 2014 08:06:24 GMT

Hi experts,

I am stuck in writing an expression for calculating the last 4 weeks total customers.

I have a data set like below

I want to display the chart which shows me week wise total customers. But the total customers must be calculated based on last four weeks. This means result should be

Week 1 - Total Customer=4

Week 2 - Total Customer=11

Week 3 - Total Customer = 18

Week 4 - Total Customer = 25

Week 5 - Total Customer= 28 and so on

Please help me in writing the set expression for this scenario.

Appreciate your help.

Re: Set Analysis For calculating Last 4 weeks total

marwen_garwachi — Fri, 21 Nov 2014 08:18:52 GMT

Hello,

you can inverse the dimensions order WEEK_IN_YEAR,CAL_YEAR

best regards

Re: Set Analysis For calculating Last 4 weeks total

SunilChauhan — Fri, 21 Nov 2014 08:23:31 GMT

sum(Customer)+above(sum(Customer)) in Expression

Re: Set Analysis For calculating Last 4 weeks total

marwen_garwachi — Fri, 21 Nov 2014 08:32:59 GMT

using above I think the result will be

Week 1 - Total Customer=0

Week 2 - Total Customer=11

Week 3 - Total Customer = 14

Week 4 - Total Customer = 14

Week 5 - Total Customer= 14 and so on

Don't you ?

Re: Set Analysis For calculating Last 4 weeks total

Fri, 21 Nov 2014 08:36:46 GMT

Thats right Marween. I need last four weeks total not the cumulative total.

Any Ideas for the expression?

Re: Set Analysis For calculating Last 4 weeks total

SunilChauhan — Fri, 21 Nov 2014 08:41:31 GMT

have you tried sum with above(sum)

sum(Customer)+above(sum(Customer))

it should give 4 if above(sum(Customer)) is 0 ,else usual

Re: Set Analysis For calculating Last 4 weeks total

jagan — Fri, 21 Nov 2014 08:41:41 GMT

Hi,

Try like this

LOAD
Cal_Year,

Week_In_Year,

Customers,

MakeWeekDate(Cal_Year, Week_In_Year) AS Date

FROM DataSource;

Now in set analysis try like this

=Sum({<Cal_Year=, Week_In_Year=, Date={'>=$(=WeekStart(Max(Date), -3))<=$(=WeekEnd(Max(Date)))'}>} Customers)

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis For calculating Last 4 weeks total

jagan — Fri, 21 Nov 2014 08:43:46 GMT

Hi,

If you are using Straight table, then try like this

Chart Properties -> Expression-> Click on Expression-> Select Accumulate checkbox and give 4 steps back.

Regards,

Jagan.

Re: Set Analysis For calculating Last 4 weeks total

Fri, 21 Nov 2014 08:45:06 GMT

Hi Ruchika,

In your chart use following steps :

Dimension 1 - WEEK_IN_YEAR

Dimension 2 - CAL_YEAR

Expression - Sum(Customers)

Check button Full Accumulation in Expression tab

Thanks,

Prakash

Re: Set Analysis For calculating Last 4 weeks total

marwen_garwachi — Fri, 21 Nov 2014 08:50:03 GMT

Did you tried to inverse dimensions ? (sum(Customers) in expression and check full accumulation button )

Re: Set Analysis For calculating Last 4 weeks total

Fri, 21 Nov 2014 12:52:53 GMT

None of the solution specified is working. Can anybody please suggest any other solution?

Thanks,

Ruchika

Re: Set Analysis For calculating Last 4 weeks total

mato32188 — Fri, 21 Nov 2014 13:10:20 GMT

Hello Ruchika,

try RangeSum ( above (total Count (Customers) , 0, 4) )

Re: Set Analysis For calculating Last 4 weeks total

VishalWaghole — Tue, 25 Nov 2014 10:12:35 GMT

Hi Ruchika,

PFA Qvw application.

Hope it will solve your problem.

-- Regards,

Vishal Waghole

Re: Set Analysis For calculating Last 4 weeks total

Tue, 25 Nov 2014 11:37:23 GMT

Thanks for the solution Vishal.

But the defined solution will only display last four months data. I want to display all the weeks prior to the selected week. So if I select 'Week 15' from listbox, then it should show all weeks data from 'Week 1' to 'Week15'. Also these must be aggregated from prior four weeks data.

So in your example, If I select 'Week 15' then Week 1 to 15 must be displayed with rolling four weeks data for each week.