topic Re: Dimensions and Expression - creating a simple Table in QlikView

Dimensions and Expression - creating a simple Table

Thu, 22 Nov 2012 10:02:08 GMT

Dear QV commuity,

today i have a (i think) basic question.

Of yourse our management is used to work in XLS and there the tables are always the same like this

JAN FEB MAR APR ...

Country1 sales

Country2 sales

etc... sales

So basically QV works with Expressions and Dimensions - in this case Months and Countries are bot Dimensions and i have to CHOOSE if i want to show them horizontal or vertical - but as you can see i need to choose INDIVIDUALLY to have one dimension horizontal and the other vertical.

Is thsi possible? seems so simple but i could not find

Thanx for your help

Seb

Re: Dimensions and Expression - creating a simple Table

its_anandrjs — Thu, 22 Nov 2012 10:06:50 GMT

Hi,

Use Straight Table or Pivot Table for this type of requirement.

Regards,

Anand

Re: Dimensions and Expression - creating a simple Table

Thu, 22 Nov 2012 10:15:33 GMT

i am using a straight table - but then i can only choose if ALL dimension should go horizontal or not - this does not help seems like a bug - because the check mark is not applied only to the selected dimension...

Re: Dimensions and Expression - creating a simple Table

its_anandrjs — Thu, 22 Nov 2012 10:22:14 GMT

Hi,

Write some thing like

=Sum(if(Months = 'Jan', Sales)) in the expression for the month if you are using straight table. Can you provide any sample file

Regards,

Anand

Re: Dimensions and Expression - creating a simple Table

Thu, 22 Nov 2012 10:24:15 GMT

hai,

pls see thebackend script for cross table

Regards,

chandu

Re: Dimensions and Expression - creating a simple Table

Thu, 22 Nov 2012 10:38:02 GMT

sorry i cannot open can you please provide screenshots of the Properties?

Re: Dimensions and Expression - creating a simple Table

its_anandrjs — Thu, 22 Nov 2012 10:42:19 GMT

Hi,

Use this sample

Temp:

load Country,Months, num( Sales,'#.##') as Sales;

load * inline

[

Country, Months, Sales

Country1, Jan, 44.09670183

Country1, Feb, 85.15861071

Country1, Mar, 109.4297193

Country1, Apr, 35.21244308

Country1, May, 39.93113584

Country2, Jun, 33.03100126

Country2, Jul, 62.78281869

Country2, Aug, 102.2187705

Country2, Sep, 89.04048974

Country3, Oct, 108.4429708

Country3, Nov, 87.25831891

Country3, Dec, 106.8788417

Country3, Jan, 44.29773026

Country3, Feb, 78.90228828

];

or in Straight table

Dimension : Country

Expression : Sum(if(Months = 'Jan', Sales)), Sum(if(Months = 'Feb', Sales)).....

Let me know

HTH

Regards,

Anand

Re: Dimensions and Expression - creating a simple Table

Thu, 22 Nov 2012 10:59:46 GMT

i am really sorry but i still cannot see how this can work -

my fields are Months (dimension), Country (dimension) sum(sales) (expression.

now i create a VERY simple straight table like above and the result is either

Jan | Country1 | 423

Jan | Country2 | 342

Jan | Country3 | 324

Feb | Country1 | 231

...

JAN | JAN | JAN | JAN | JAN |

C1 C2 C3 C4

232 232 232 232

Both is not acceptable

Re: Dimensions and Expression - creating a simple Table

bharatkishore — Thu, 22 Nov 2012 11:56:03 GMT

HI scdrawert,

Please Check the attached file. I hope this is the solution what you are looking for

Re: Dimensions and Expression - creating a simple Table

bharatkishore — Thu, 22 Nov 2012 12:30:08 GMT

scdrawert,

We can use generic load concept and display in table box. Check the attached file.

Re: Dimensions and Expression - creating a simple Table

Thu, 22 Nov 2012 16:56:05 GMT

I do this all the time for sales reports. It's always a PIVOT table, and I make the vertical dimension the sales rep, and the horizontal dimension the month. The expression is then simply =sum(SalesAmount).

Note to QV: Please give us a keyboard shortcut to "pull" a dimension up to the horizontal. The "grab and drop" feature is very tempermental; I find I have to do it five or six times before I get it to work. (There's actually a table way below, but this lousy editor won't let me delete the intervening white space; you'll have to scroll down to see it)

Sales Rep	Close Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
EKES		$98,663	$41,347	$449,277	$116,679	$248,473	$252,084	$103,402	$335,078	$268,779	$423,682	$23,400	$0	######
JSIL		$27,000	$0	$43,100	$90,600	$8,140	$4,000	$3,500	$85,750	$180,965	$8,000	$0	$0	$451,055
MCRO		$2,820	$20,965	$30,360	$33,183	$15,319	$105,259	$26,602	$5,200	$116,282	$43,099	$19,890	$0	$418,979
PDEV		$73,170	$56,530	$29,991	$0	$0	$13,639	$11,412	$54,700	$78,617	$81,500	$0	$0	$399,559
RCAN		$14,340	$50,688	$20,052	$18,000	$8,620	$6,980	$1,500	$6,040	$146,412	$69,250	$32,000	$0	$373,882
SBAR		$137,742	$18,864	$19,139	$8,160	$30,720	$0	$105,227	$3,000	$6,430	$6,500	$0	$0