# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Contributor II

## Average Stock

Hi all,

I have developed Inventory application, and i brought closing stock value as of now date.

But I need to bring the average stock value, by the following method

Closing Stock(whole stock value for a month)/No. of Days

my closing stock expression

sum({<Date={"<=\$(vDate)"}, FinYear=, FinMonth=, Date=, EntryType={Purchase}>} Stock)

+

(sum({<Date={"<=\$(vDate)"}, FinYear=, FinMonth=, Date=, EntryType={Sales}>} Stock))

+

sum({<Date={"<=\$(vDate)"}, FinYear=, FinMonth=, Date=, EntryType={Damage}>} Stock)

+

sum({<Date={"<=\$(vDate)"}, FinYear=, FinMonth=, Date=, EntryType={Return}>} Stock)

---vDate = max(Date)

for the avg stock, my expression is as

above cl.stock expression / count(distinct Date)

but the avg values arrived are wrong, b'coz, in my expr. i used max date, so if i select any year, month...

stock value shows the respective monthend date cl.stock....

so the avg calculates by taking the cl.stock value and divide by no. of days selected, but it should not be like this....

avg should be arriving by this type

calculate the stock sum of whole selected month and divide it by no. of days

its very urgent...

Thanks,

Kalyan

1 Solution

Accepted Solutions
Contributor II

## Re: Average Stock

hi all,

i had calculated the average by using the following expression

rangesum(above(total Stock, 0, rowno(Total)))/count(distinct date)

the expression produce the average correctly, only when we use date field as a dimension.

If i remove date from dimension, the average calculates as i had told in the original discussion

i.e. average takes max.date stock and divide the no. of days selected

so again am stucked for a long while.

Kalyan

5 Replies
MVP

## Re: Average Stock

Kalyan,

you shouldn't use Date field selection multiple times in your set modifier, since you set Date according your variable, remove the 'Date=, ' part in your set expressions.

If you need to know the day number of your max date within the month to calculate your average, you can also just use

=day(vDate)

Contributor II

## Re: Average Stock

hi Swuehl,

I understood the logic what you have told, but regarding the day(vdate), i dont know how to put in the set...

Thanks,

Kalyan

Valued Contributor

## Re: Average Stock

If you want AVERAGE_STOCK = STOCK_AT_THE_END_OF_EACH_DAY / NO_OF_DAYS then you have two choises:

1. Create a separate time line (independent) and use it to calculate stock at the end of each day (heavy-duty calculation in interface when used, suited for a large number of transactions)

2. Calculate in script all the stocks at the end of each day in a separate table and read the values from it (suited for a smaller number of transactions)

Contributor II

## Re: Average Stock

hi all,

i had calculated the average by using the following expression

rangesum(above(total Stock, 0, rowno(Total)))/count(distinct date)

the expression produce the average correctly, only when we use date field as a dimension.

If i remove date from dimension, the average calculates as i had told in the original discussion

i.e. average takes max.date stock and divide the no. of days selected

so again am stucked for a long while.

Kalyan

Contributor II

## Re: Average Stock

hi Swuehl

I had tried without using the date in set expressions. but it is not yet solved.