topic Re: Average Stock in QlikView

Average Stock

kalyandg — Wed, 03 Jul 2013 14:09:14 GMT

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

can anyone please help me..

its very urgent...

Thanks in Advance to everyone who ever reply to me...

Thanks,

Kalyan

Re: Average Stock

swuehl — Wed, 03 Jul 2013 18:40:04 GMT

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)

Re: Average Stock

kalyandg — Thu, 04 Jul 2013 15:02:45 GMT

hi Swuehl,

Thanks for your reply.

Please let me know how to put the expression or please type the expression in your reply.

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

please reply with the expression.

Thanks,

Kalyan

Re: Average Stock

luciancotea — Thu, 04 Jul 2013 15:20:40 GMT

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)

Re: Average Stock

kalyandg — Tue, 09 Jul 2013 08:49:45 GMT

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.

Please help me to solve the issue.

Thanks in Advance,

Kalyan

Re: Average Stock

kalyandg — Wed, 10 Jul 2013 06:00:52 GMT

hi Swuehl

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

please help me

Thanks,

Kalyan