topic Creating a set of data in QlikView

Creating a set of data

Wed, 24 Mar 2010 09:29:08 GMT

Hi,

I have the following tables in qlikview

budget

payment

dateisland - a listing of all dates including month and year that are in either the budget or payment table.

What I would like to do is create a set of data containing the following columns:

month name, month number, sum(payment)

Here's the twist ... I would like the set(s) to be as follows

1|jan|sum(payments in jan)

2|feb|0

3|mar|0

... until dec.

1|jan|sum(payments in jan)

2|feb|sum(payments in feb)

3|mar|0

... until dec.

1|jan|sum(payments in jan)

2|feb|sum(payments in feb)

3|mar|sum(payments in mar)

4|apr|0

... until dec

and continue this pattern for all months in a selected year.

In SQL, not a problem, (lengthy, but possible). My question is how can this be done in qlikview either in the script or via set analysis?

Thanks in advance for any help/suggestions.

Creating a set of data

Wed, 24 Mar 2010 09:44:23 GMT

pls give structure of budget, payment and dateisland tables...

i think you ve date field also in Payment table...in that case, you can use sm functions like month()or monthname() to get the month number and month name and u can use sum() to get payment for a particular selected year...

after that you can take monthname as a dimension in qv chart,

if you ve selected two years, then you can take monthname() as dimension,,,it will give like Jan 2009, Jan 2010 etc

Creating a set of data

Wed, 24 Mar 2010 09:52:20 GMT

Hi Arun,

Thanks for the reply.

Here is a simplified structure of the budget and payment tables.

Budget

budgLine|budgetedDate|budgetedAmount|budgetDateIdentifier

Payment

budgLine|paymentid|paymentdate|paymentAmount|payDateIdentifier

dateisland

dateidentifier

year

monthnumber

monthname

All of the report works well, it is just trying to create the set of data that has been requested which is causing me some issues.

Thanks

Creating a set of data

Wed, 24 Mar 2010 10:01:06 GMT

So u ve paymentDate in Payment table

u can use Year(paymentDate), month(paymentDate), and Monthname(paymentDate) to create 3 more fields into script.

Then u can use Monthname(paymentDate) as a dimension and sum(paymentAmt) as a expression in a chart.

You can give a selection for Year(paymentDate) at UI level.

As soon as u select a year, all months from that year will be there in the chart showing sum of payments.

Creating a set of data

Thu, 25 Mar 2010 09:37:26 GMT

Hi Arun,

Unfortunately, i am trying to animate a year's payments which is why I need to create the set of data as I described in my original post.

I do know that I could draw the chart as you outline above, but in this case, it is a rather special request/requirement.