cross table problem

[#1]

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

[MK_QSL]

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

=COUNT(DISTINCT NAME)

[its_anandrjs]

Write like

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

=Count (DISTINCT NAME)

[#4]

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 ...

[MK_QSL]

In your script, in stead of APRIL use below

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

[its_anandrjs]

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

[MK_QSL]

Only Helpful?

[its_anandrjs]

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

[#9]

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..?

[its_anandrjs]

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])