topic Re: straight table in QlikView

straight table

denis115 — Wed, 25 Nov 2020 16:16:04 GMT

Hello!

I need help!

I have a table where I have :

prodname

sales_sum

start sales

last sales

and when I select big period of time I need to see in : sales sum (sum of sales in this period) , start sales (sales of first month selected)

and last sales (sales of last month selected)

how can I do this?

Thank you!

Re: straight table

mdmukramali — Tue, 10 Oct 2017 08:34:27 GMT

Hi,

You can Use Calendar Object.

See here for a video tutorial on just that subject.

Thanks,

Mukram.

Re: straight table

kenphamvn — Tue, 10 Oct 2017 08:39:10 GMT

try expression like this

sales_sum: sum(Sales)

start sales: sum({<Month={"=$(=Min(Month))"}>} Sales)

last sales: sum({<Month={"=$(=Max(Month))"}>} Sales)

regards,

Re: straight table

denis115 — Tue, 10 Oct 2017 08:48:12 GMT

there is an error in expression

Re: straight table

tresB — Tue, 10 Oct 2017 08:51:28 GMT

Could you share your sample qvw?

Re: straight table

HirisH_V7 — Tue, 10 Oct 2017 08:53:54 GMT

Hi,

i hope this three kind of expressions will give you a desired result,

Sum({<MonthNum={'$(=Min({<Year={"$(=Min(Year))"}>}MonthNum))'}>}Sales)
Sum(Sales)
Sum({<MonthNum={'$(=Max({<Year={"$(=Max(Year))"}>}MonthNum))'}>}Sales)

you need to have your date calendar having monthnum column and year as above.

HTH,

PFA,

Hirish

Re: straight table

denis115 — Tue, 10 Oct 2017 09:43:31 GMT

Can I do it with my variabiles?

Re: straight table

HirisH_V7 — Tue, 10 Oct 2017 09:47:37 GMT

Yes! you can ,

But you need to check there are working as expected, By checking them in text box or in straight table itself by hovering on expression(Expression should not be labeled).

Re: straight table

denis115 — Tue, 10 Oct 2017 09:49:18 GMT

test

Re: straight table

HirisH_V7 — Tue, 10 Oct 2017 09:52:05 GMT

* I have made changes in my above post . Please check.

Re: straight table

tresB — Tue, 10 Oct 2017 10:05:15 GMT

Can't open. Could you check and may be share a smaller one?

Re: straight table

denis115 — Tue, 10 Oct 2017 10:16:49 GMT

TEST

Re: straight table

tresB — Tue, 10 Oct 2017 10:49:25 GMT

Which fields are date, month, year, sales?

Re: straight table

denis115 — Tue, 10 Oct 2017 11:21:54 GMT

I have start date "Начало месяца" and last date "Конец месяца" as you can see, only at last sheet I need to have in

columns "ОНН" Sum ([Ост.Начало Стоим.]) for lower month of selection and in "ОНК" the higher month of selection

for example I select : apr,may,june,july in col OHH I NEED TO SEE SUM ([Ост.Начало Стоим.]) only apr because it is lower month from selection

Re: straight table

tresB — Tue, 10 Oct 2017 11:50:40 GMT

Try:

Sum ( {<[Начало Месяца]={"$(=Min([Начало Месяца]))"}>}[Ост.Начало Стоим.])

Sum ( {<[Начало Месяца]={"$(=Max([Начало Месяца]))"}>}[Ост.Начало Стоим.])

Note: If you wish to select from another date field, you might have to include those date fields as well in the expression to nullify it's effect. like:

Sum ( {<[Конец Месяца], [Начало Месяца]={"$(=Min([Начало Месяца]))"}>}[Ост.Начало Стоим.])

Sum ( {<[Конец Месяца], [Начало Месяца]={"$(=Max([Начало Месяца]))"}>}[Ост.Начало Стоим.])

Re: straight table

denis115 — Tue, 10 Oct 2017 12:01:55 GMT

Thank you!!! it works!

Re: straight table

HirisH_V7 — Tue, 10 Oct 2017 12:03:44 GMT

Bro u know that language super.