topic Presenting data in a table in QlikView

Presenting data in a table

linoyel — Wed, 26 Feb 2014 11:57:59 GMT

Hi all,

I need your help in presenting data in the way the business wants to see it.

I have different products (a,b,c,d,e) and I also know their Costs and Sales sum.
Product is a dimension, and Costs and Sales are expressions.

Here is an example of a straight table for it:

Product	Cost ($)	Sales ($)
	247,198	1,607,900
a	0	30
b	5	40
c	10	50
d	20	25
e	0	20

I also have a table that keeps Budget values for Costs and Sales per every month. for the year of 2014.

At the end, I need a report to be looking like this:

a + b
	Cost ($)	Sales ($)
Budget (given value)	100	150
Actual	5	70
c +d
	Cost ($)	Sales ($)
Budget (given value)	150	300
Actual	30	75
e
	Cost ($)	Sales ($)
Budget (given value)	50	100
Actual	0	20
Total
	Cost ($)	Sales ($)
Budget (given value)	300	550
Actual	35	165

It has to present data from the beginning of the current month up to yesterday's date, and to be sent by e-mail every day.

1. Which kind of table is right to use in order to get data presented in the way I need?

2. I guess I need to use Set Analysis?

3. How do I sent the table by e-mail?

Please guide me, any help will be appreciated!

Thanks

Re: Presenting data in a table

stevedark — Wed, 26 Feb 2014 13:09:54 GMT

Hi Linoy,

The best way to get this to send via email is with a third party product. The one we use, resell, and highly recommend is NPrinting.

With this tool you would be able to create four separate tables in QlikView, with Set Analysis as you rightly assume, and then drop those tables into an Excel spreadsheet in the format that you require.

NPrinting then handles the distribution of this in either Excel or PDF format.

I would suggest concatenating your budget figures into the same table as actuals, just with fields of BudgetCost and BudgetSales. On each row you would also need a RowType field of Budget or Actual.

You would then need to create a Table with RowType as a dimension and expressions like this:

=sum({<Product={'a','b'}>}Cost + BudgetCost)

and

=sum({<Product={'a','b'}>}Sales + BudgetSales)

Hope that helps point you in the right direction.

Regards

Steve Dark

Re: Presenting data in a table

linoyel — Wed, 26 Feb 2014 13:18:26 GMT

STEVE, THANK YOU!