topic Re: Using the current opening balance in QlikView

Using the current opening balance

Wed, 22 Aug 2012 08:47:28 GMT

Hi folks,

For some reason what i thought would be quite simple is eluding me.

I have a table which captures a monthly opening balance based on a global region and how many tradesman within that region

Date	Location	EcType	Amount
01/08/2012	Rest of World	Builders	66
01/07/2012	United Kingdom	Electricians	12374
01/08/2012	United Kingdom	Electricians	12376
01/07/2012	China	Electricians	60
01/08/2012	China	Electricians	70
01/08/2012	China	Builders	100
01/07/2012	Italy	Electricians	61
01/08/2012	Italy	Electricians	62

All im trying to display is the current months amount of tradesman by region , and then break that down by tradesman type. The bit thats eluding me is getting the value for the current month ni the set analysis equation

I'll also have a few date list boxes, which break down by years and month

using Location as a dimension Im using something along the lines of

=sum({$<Month(Date) = Month(Today(), Year(Date) = Year(Today())}>} DISTINCT Amount)

This work to a degree but only if something is selected on the date list box

All im after is something like

Current Tradesmen	Amount
date 01/08/2012
Rest of World	66
United Kingdom	12376
China	170
Italy	62

But this box will show the current month values regardless of whether anything is selected

many thanks

Re: Using the current opening balance

RedSky001 — Wed, 22 Aug 2012 10:11:06 GMT

Hi leewilliams,

You could create a pivot table with Date & Location as your dimensions.

Then add the following expresison.

sum({1 < Date = {'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'} >} Amount)

Think of creating a master calendar. There are a lot of good posts and an entry in the Wiki on how to get this done.

Mark

Re: Using the current opening balance

Thu, 23 Aug 2012 10:33:00 GMT

HI Mark

Thanks for the response

I think you meant the dimensions should be location and ECType .. no?

Either way I have tried it with both date and ECType as dimension and using

=Sum ({1 < Date = {'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'} >}OBAmount)

I just get 0 as the answer

I think i understand what the set analysis expression is trying to do but its just not bringing back the right answer

the only difference between the value of Date and today() i could see was 'Date ' was a date/time value and today() was just a date . Would that make a difference ?

I already have a master calender in the structure with a trans table linking the other tables to it , just havent dont that with this table yet.

Lee

Re: Using the current opening balance

RedSky001 — Thu, 23 Aug 2012 10:40:54 GMT

Hi Lee,

See the attached qvw and let me know if this helps.

Markl

Re: Using the current opening balance

Thu, 23 Aug 2012 11:04:42 GMT

HI Mark

Appreciate all the help and your table helped immensley, thank you. The issue was around the date field being joined to the calendar with the same field name

As a stand alone table you method works perfectly well

but then if i link it to the master calendar and so I can do an opening balance over time chart, the over view table falls over. I can probably address that by using the trans link table

thanks

Re: Using the current opening balance

Thu, 23 Aug 2012 15:37:37 GMT

As i thought as soon as I linked it back to the master calendar it fell over again. At least this time it was showing me some values

with date being on the master calendar and linked to the opening balance table through an ID table

using

=Sum({1<Date={'=$(=MonthStart(Today()))'} >}OBAmount)

produced opening balance values for every month

using

=Sum ({1 < Date = {'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'} >}OBAmount)

just didnt' bring back any values at all.

Can't believe something this simple in SQL is so complicated.

Re: Using the current opening balance

RedSky001 — Thu, 23 Aug 2012 15:47:26 GMT

Can you upload an example qvw file?

Re: Using the current opening balance

Fri, 24 Aug 2012 10:11:32 GMT

Hi Mark

Sorry its company information which i cant upload, but heres the scehma

The chart is a pivot using dimensions from

date from table ECdate

and OBlocation from OpenECBalance

and expression

=Sum({1<Date={'=$(=MonthStart(Today()))'} >}OBAmount)

The ECTrans table is created in SQL and then called via a stored proc into qlikview

OpenECBalance looks akin to this , but ive had to change actual values but not column headings

OBID	OBAmount	EcType	OBLocation
379	16	Builder	Rest of World
380	638	electrician	Rest of World
381	8	plumber	United Kingdom
382	806	brickie	United Kingdom
383	3359	labourer	United Kingdom
384	138	Builder	United Kingdom
385	12254	foreman	United Kingdom
386	3102	electrician	United Kingdom
387	38531	Carpentar	United Kingdom
388	40	foreman	China
389	4	electrician	China
390	54	Carpentar	China
391	17	foreman	India
392	1	electrician	India
393	40	labourer	India
394	146	Carpentar	India
395	644	electrician	Rest of World
396	16	Builder	Rest of World
397	1224	foreman	Rest of World
398	8241	Carpentar	Rest of World
399	63	brickie	Rest of World
400	274	labourer	Rest of World
401	3103	electrician	United Kingdom
402	8	plumber	United Kingdom
403	153	Builder	United Kingdom
404	38641	Carpentar	United Kingdom
405	3379	labourer	United Kingdom

dotn know how helpful this is or how to quickly generate something similar quickly

Re: Using the current opening balance

Fri, 24 Aug 2012 14:30:38 GMT

I found another way of doing it using this method which works perfectly for me , thanks for your help mark

http://community.qlik.com/message/2702#2702