topic Re: cross table problem in QlikView

cross table problem

Mon, 28 Apr 2014 10:43:56 GMT

Dear All,

In my excel I have months wise amount is avaliable and when I use cross table for using months table..

I am not able to find right count and Sum of NET Amount..

Plz find attachment for details..

Rahul

Re: cross table problem

MK_QSL — Mon, 28 Apr 2014 10:51:12 GMT

=num(SUM(Aggr(SUM(DISTINCT [NET BAL]),NAME)),' #,##0')

=COUNT(DISTINCT NAME)

Re: cross table problem

its_anandrjs — Mon, 28 Apr 2014 11:01:08 GMT

Write like

=Sum( Aggr( sum(DISTINCT Data),NAME))

=Count (DISTINCT NAME)

Re: Re: cross table problem

Mon, 28 Apr 2014 11:02:39 GMT

Dear Manish,

After applying ur formula's I got...

Sum of Net Amount is 188500 in excel but on applying to qvw I got 49990 and

Total DISTINCT count in excel is 13 but I got 5 in qvw ...

Re: cross table problem

MK_QSL — Mon, 28 Apr 2014 11:09:03 GMT

In your script, in stead of APRIL use below

IF(LEN(TRIM(APRIL))=0,0,APRIL) as APRIL,

Re: cross table problem

its_anandrjs — Mon, 28 Apr 2014 11:17:05 GMT

Your cross table script is not correct add Rowno() in the table like below

Table:

CrossTable(mONTHS, Data, 😎

LOAD NAME,

[LA. BAL.],

[NEW INS],

DATE,

[NET BAL],

BALANCE,

CRADIT,

BALANCE1,

APRIL,

MAY,

JUNE,

JULY,

AUGUST,

SEPTEMBER,

OCTOBER,

NOVEMBER,

DECEMBER,

JANUARY,

FEBRUARY,

MARCH,

RowNo() as id

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

And then calculate

=Sum( Aggr( sum(DISTINCT [NET BAL]),NAME)) it gives 188500

=Count (DISTINCT NAME) it gives 13

Re: cross table problem

MK_QSL — Mon, 28 Apr 2014 11:24:24 GMT

Only Helpful?

Re: cross table problem

its_anandrjs — Mon, 28 Apr 2014 11:29:24 GMT

Did you check my suggestion in the load statement i believe it fulfill your requirement check once and load according .

And on a text box 1 plot =Sum( Aggr( sum(DISTINCT Data),NAME))

and in second text box plot =Count (DISTINCT NAME)

Regards

Re: Re: Re: cross table problem

Mon, 28 Apr 2014 12:42:08 GMT

Dear Manish/Anand,

plz find attachment now..

I have city OO and WW and I want separately Net amount total of WW and OO..

I want to make a pie chart for NET amount where DIM = CITY....

and I also want to see total name count = 27 (not DISTINCT)...is it possible..?

Re: Re: Re: cross table problem

its_anandrjs — Mon, 28 Apr 2014 12:58:32 GMT

In your application city field is not added add it and also you have only 23 distinct name and then take a pie chart

Dimension:- CITY

Expression:- =Sum({<CITY = {'OO','WW'}>}[NET BAL])

Re: cross table problem

MK_QSL — Mon, 28 Apr 2014 13:20:57 GMT

Check enclosed file..

Re: cross table problem

Mon, 28 Apr 2014 16:23:58 GMT

Thanks Anand....