22 Replies Latest reply: May 23, 2014 7:31 AM by Alexei Serdiuk

# How to divide the quantity of store by its consumption during last three months

Hi, everybody!

I need to divide the quantity of store by its consumption during last three months.

I'm a very beginer, so I bag your pardon for this way of expressing my idea:

sum([Quantity])/(sum({<MonthName=MonthOfConsumption-1>} [Consumption]) + (sum({<MonthName=MonthOfConsumption-2>} [Consumption]) + (sum({<MonthName=MonthOfConsumption-3>} [Consumption])

Thank you VERY MUCH!

• ###### Re: How to divide the quantity of store by its consumption during last three months

Hi, Alexei

What problem you faced on?

The logic of your expression is quite right, there are only syntax mistakes

sum([Quantity])/

((sum({\$<MonthName={\$(=MonthOfConsumption-1)}>} [Consumption]) +

(sum({\$<MonthName={\$(=MonthOfConsumption-2)}>} [Consumption]) +

(sum({\$<MonthName={\$(=MonthOfConsumption-3)}>} [Consumption])

)

Hope this helps.

Regards,

Andrei

• ###### Re: How to divide the quantity of store by its consumption during last three months

Андрей, спасибо за Вашу помощь!

Я попробовал вставить в диаграмму часть предложенной Вами формулы. Ожидал, что получу расход материала, сдвинутый на один месяц. А получил расход материала без сдвига.

Где я ошибся?

Еще раз спасибо!

• ###### Re: How to divide the quantity of store by its consumption during last three months

Alexiej,

do you have field named: MonthName?

I think you need:

sum({<MesjacRashoda={\$(=MesjacRashoda-1)}>}Raschod-kol-po)

of course pun your field names as they are in application.

Darek

• ###### Re: How to divide the quantity of store by its consumption during last three months

Unfortunately, this metod doesn't work too...

Maybe I should try to do this in table?

Where am I mistaken?

Thank you!

• ###### Re: How to divide the quantity of store by its consumption during last three months

Maybe you may attach your application with sample data?

• ###### Re: How to divide the quantity of store by its consumption during last three months

go to staraight table and put simple \$(=MiesjacRashoda) as expression. Look what result you get.

• ###### Re: Re: How to divide the quantity of store by its consumption during last three months

Dariusz, I don't know why I can't succeed.

Of course I can attach the application.

And thank you very much! You're spending so much time for me!

• ###### Re: Re: How to divide the quantity of store by its consumption during last three months

Dariusz, I don't know why I can't succeed.

Of course I can attach the application.

And thank you very much! You're spending so much time for me!

• ###### Re: How to divide the quantity of store by its consumption during last three months

Alexei,

with this expression i got result:

I reccomend you to find on community something about master calendar. The best way to have it easier is to load own periods numbers. Then you may easy calculate on those numbers.

regards

Darek

• ###### Re: How to divide the quantity of store by its consumption during last three months

Darek, thank you very much!

It's a sort of misticism, but I failed...

• ###### Re: Re: How to divide the quantity of store by its consumption during last three months

regards

Darek

• ###### Re: How to divide the quantity of store by its consumption during last three months

have you seen my attachment?

• ###### Re: How to divide the quantity of store by its consumption during last three months

I can't open it because of using Personal Edition...

• ###### Re: How to divide the quantity of store by its consumption during last three months

Thank you very much!

I'll keep studying the question.

• ###### Re: How to divide the quantity of store by its consumption during last three months

Добрый день, Алексей.

Результат получился несмещенный из-за преобразований форматов Date->Int, а  в обратную сторону уже не очень красиво получается. В приложении Ваш файл, с добавленным календарем.

У меня получилось 2 типа графика:

1. Выбираешь 1 дату для МесяцРасхода - получаешь со смещением (как советовал Dariusz)

2. Выбираешь любой месяц и любое количество, которое нужно и получаешь график по Расходу.

Выбирайте, что Вам/Заказчику удобнее.

Также используя выражения \$, \$1, \$2 в Set Analysis можете получать таблицы с динамически подставляемыми датами, т.е. щелкаете по фильтрам сен, потом авг, потом май к примеру, получите суммы за каждый из месяцев.

Надеюсь это поможет.

Удачи.

Андрей

• ###### Re: How to divide the quantity of store by its consumption during last three months

Андрей, огромное Вам спасибо!

К сожалению, не смог открыть Ваш файл - QlikView не рекомендует это делать. Или можно?

Простите за такой примитивный вопрос - я сам и Заказчик, и Исполнитель, и чайник в одном лице.

• ###### Re: How to divide the quantity of store by its consumption during last three months

Алексей, открыть-то можно, но как и предупреждает QV только 5 разных отчетов (файлов).

Дальше нужна лицензия, но Вы же и сами понимаете, что всё решается, нужно только захотеть.

Андрей.

• ###### Re: How to divide the quantity of store by its consumption during last three months

Андрей, большое спасибо!

Буду учиться дальше.

• ###### Re: How to divide the quantity of store by its consumption during last three months

Alexei,

typpical data model for this will be something like this:

store, date, consumption

store1, 2014-01-01, 10

store1,2014-01-02, 12

store2, 2014-01-01, 34

......

In this model :

a) expression:

count(distinct store)

will give you stores number

b) expression

sum({<date={">='2014-01-01<=2014-03-31"}>}consumption)

will give you consumption from 1-01 to 31-03.

So, you final expression may be something like this:

count(distinct store)/sum({<date={">='2014-01-01<=2014-03-31"}>}consumption)

There still is the question and it deppends on you particular needs, how to get those dates for set analysis.

regards

Darek

• ###### Re: How to divide the quantity of store by its consumption during last three months

Dariusz, thank you!

Unfortunately, it doesn't work...

Maybe, I misunderstood you?

• ###### Re: Re: How to divide the quantity of store by its consumption during last three months

Hi Alexei,

It doesn't work because you have date in format like "31.03.2013" and in your last expression you are trying to compare it with "2013-01-01. Thus it doesn't work.

PFA

• ###### Re: Re: How to divide the quantity of store by its consumption during last three months

Sergey, thank you very much!