topic Re: Help with linear table in QlikView

Help with linear table

Thu, 12 Jul 2012 09:18:23 GMT

Hi guys,

I have some problem with a linear table.

I would like to post a qvw file but unfortunately I can't so I try to explain my problem and I post an xls file to be as clear as I can.

I'm playing with sap data.

My goal is to find an expression to calculate TOTAL COST PER TRAVEL.

So, up to now, I have this expressions that sound ok:

- total Cost per CDC (CDC = Cost Center)

- total Km per CDC

- unit cost per KM

- CDC cost per travel

What I'm looking for is to calculate total cost per travel.

In my last expression I have "CDC cost per travel". Now I want to sum those costs per single travel.

I tried with:

=aggr( sum(Column(4)), VBELN )

but it doesn't work.

I attached an xls file to better undrstand my problem.

I exported my linear table on it.

I'll appreciate any suggestion.

Best regards

Giampiero

Re: Help with linear table

Thu, 12 Jul 2012 10:02:23 GMT

I am not sure but did you try: sum(total <VBELN> Column(4))?

Kiran.

Re: Help with linear table

Thu, 12 Jul 2012 10:06:51 GMT

Hi Kiran,

Yes, I did.

Unfortunately it doesn't work.

Thank you very much.

Giampiero

Re: Help with linear table

Thu, 12 Jul 2012 10:22:58 GMT

=sum(aggr(sum(distinct {<VBELN=>} VG_CO.WKGBTR)/sum(distinct {<VBELN=>} ZZ_KM_ESTT),Doc_CO.CDC)*ZZ_KM_ESTT)

May be.

Kiran.

Re: Help with linear table

Thu, 12 Jul 2012 10:30:58 GMT

Unfortunately it doesn't work.

I'm really appreciating your help Kiran.

Giampiero

Re: Help with linear table

Thu, 12 Jul 2012 10:59:29 GMT

Probably my last try:

sum(TOTAL <VBELN> aggr(sum(TOTAL <Doc_CO.CDC> VG_CO.WKGBTR)*ZZ_KM_ESTT/sum(TOTAL <Doc_CO.CDC>ZZ_KM_ESTT),Doc_CO.CDC,VBELN))

Kiran.

Re: Help with linear table

Thu, 12 Jul 2012 11:06:06 GMT

Kiran,

it still doesn't work but it seems to be a good way.

Thanks

Giampiero

Re: Help with linear table

Thu, 12 Jul 2012 11:18:09 GMT

For the results in excel shared can you share core values?

Basicaly what needs to be done is to try and seperate the table in qv datamodel to aviod the total function.

Regards,

Kiran Rokkam.

Re: Help with linear table

Thu, 12 Jul 2012 11:24:28 GMT

you can't cheat dimension on table (aggr, set analysis.....)

can't you use pivot table and total ?

Re: Help with linear table

Thu, 12 Jul 2012 11:26:15 GMT

Kiran,

I think that your idea to work on the data model is a chance.

I thought to separate the tables, and now I think to try it.

Regards

Giampiero

Re: Help with linear table

Thu, 12 Jul 2012 11:34:21 GMT

Kiran

I attached an image of my data model.

i'm playing with these three tables:

CIS, VG_CO,DOC_CO

Hope it helps

Giampiero

Re: Help with linear table

Thu, 12 Jul 2012 11:59:00 GMT

Hi Pari,

I tried to use Pivot table but my problem still resist.

Thanks for your reply

Re: Help with linear table

Thu, 12 Jul 2012 12:21:23 GMT

Hi Kiran,

you were right.

The expression correct is:

=sum(TOTAL <VBELN> aggr((sum (distinct total <Doc_CO.CDC> VG_CO.WKGBTR) / sum (distinct total <Doc_CO.CDC> ZZ_KM_ESTT) ) * ZZ_KM_ESTT,Doc_CO.CDC,VBELN))

THANK yOU so much for your help.

I really appreciated

Best regards

Giampiero