# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

## Sum based on limited occurences

Hi guys,

I should know this but I can't get it to work properly.

I want to sum sales data from today (vToday already calculated) until the 30th occurrence of a working days and only the when working days are equal below. Using the table below, the result should be 4099.

I use a normal calendar tables linked to a sales table. Something like:

 Date Working_days Sales 17/03/2014 (Today)1 211 18/03/2014 1 21 19/03/2014 1 72 20/03/2014 1 147 21/03/2014 1 181 22/03/2014 0 15 23/03/2014 0 0 24/03/2014 1 157 25/03/2014 1 238 26/03/2014 0 0 27/03/2014 1 100 28/03/2014 1 42 29/03/2014 0 5 30/03/2014 0 209 31/03/2014 1 196 01/04/2014 1 42 02/04/2014 1 213 03/04/2014 1 248 04/04/2014 1 2 05/04/2014 0 4 06/04/2014 0 243 07/04/2014 1 216 08/04/2014 1 8 09/04/2014 1 212 10/04/2014 1 215 11/04/2014 1 21 12/04/2014 0 150 13/04/2014 0 91 14/04/2014 1 216 15/04/2014 1 8 16/04/2014 1 212 17/04/2014 1 215 18/04/2014 1 21 19/04/2014 0 150 20/04/2014 0 91 21/04/2014 1 196 22/04/2014 1 42 23/04/2014 1 213 24/04/2014 1 248 25/04/2014 1 2 26/04/2014 0 4 27/04/2014 0 243 28/04/2014 (30th occur.)1 196 29/04/2014 1 42 30/04/2014 1 213

Desired result = 4099

Can anyone help out?

Kind regards,

Nuno

Tags (2)
1 Solution

Accepted Solutions
MVP

## Re: Sum based on limited occurences

see attachment

9 Replies
Honored Contributor III

## Re: Sum based on limited occurences

Hi Nuno,

*,

dual(date(Datefield, 'MMM-YYYY'), MonthEnd(Datefield)) as MonthYear,

From tablename;

Set the variable like

vStartYear = MonthStart(max(MonthYear),-9),

vEndYear = MonthEnd(max(MonthYear))

expression like this

Sum({<SalesDate_MonthYear={">=\$(StartYear) <=\$(EndYear)"}>} SalesNetPrice)

Note : Suppose if your data available for oct-2013  only so max date is oct-2013. so only the put the condition like MonthStart(max(MonthYear),-9).

Thanks,
AS

Not applicable

## Re: Sum based on limited occurences

Thank you amit,

but that is how you do a simple range date calculation. Your answer does not cover summing by working days or limiting it to a give number of working days.

Kind regards,

Nuno

Not applicable

## Re: Sum based on limited occurences

Valued Contributor III

## Re: Sum based on limited occurences

Hi Nuno, i am also trying the solution for you. If you get any please post the solution. but sure i will try to get something for youu

Valued Contributor III

## Re: Sum based on limited occurences

Hi Nuno are you pulling these dates and working days from any database, it would be easy to do in database. Please let me know if that is the case

Not applicable

## Re: Sum based on limited occurences

Hey Nuno,

When I did as per your requirement I got 4111 as the result. I didn't use today as that would be March 20.

Per your logic I added everything from 17/3/2013 to 28/4/2014 where Working_days =1

I created a few flags to find out the 30th day. It should be tweaked for today though.

Hope it helps

Thanks

AJ

MVP

see attachment

Not applicable

## Re: Sum based on limited occurences

Hi Vinay,

They are in a database but I don't have access. Neither the script should be modified.

The approach I'm looking for is through an expression like:

Sum({<Dater={">=\$(=vToday0) <=\$(=v30workdays)"}>} Sales)

The vWorkingDays is the variable I want to define...

Thank you so much for the help.

Kind regards,

Nuno

Not applicable

## Re: Sum based on limited occurences

Thanks Massimo. Thanks everybody.

This could do the trick. I would prefer not to have script changes but they are minimal.

Kind regards,

Nuno