Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Lee,
See the attached qvw and let me know if this helps.
Markl
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
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.
Can you upload an example qvw file?
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
I found another way of doing it using this method which works perfectly for me , thanks for your help mark