20 Replies Latest reply: Oct 26, 2012 2:25 AM by Anju Gopinath

# Adding expression after totals

Hi!

I had a trouble with a pivot table. I had calculated totals (using partial sum). Now I want to add an expression 'Penetration' after totals column.But the problem is, since my columns were obtained by dragging rows to look like columns, this new expression (or column) appears after every column...

Is there any way to solve this? Right now , I have created another table only to show 'Penetration'. and have placed them together. But, this will give me problems if I want to export these tables as excel as I would want it in a single excel sheet..

Or alternately, is there any way I can combine two charts into one excel sheet?

Earlier, I had posted some questions for this same problem....I have mentioned the link below.

Please scroll towards the end to see my questions.

Thanks ,

Anju

• ###### Re: Adding expression after totals

Cud u post ur sample qvw ?

Regards

• ###### Re: Adding expression after totals

Hi qlik_techie,

Thanks a lot for looking into this!

-Anju

• ###### Re: Adding expression after totals

Hi Anju,

Find the attached application.

Try out. Modify it as per your requirement.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Tom,

Thanks a lot for your help...It has solved my problem to some extent. Right now, I am checking whether the logic will work in case of more than one year....so across a row, my labels should look like 'Jan 2012' , 'Feb 2012'.......'Jan 2013','Feb 2013'....etc...

Also will be trying to see if the month and year extraction can be made dynamic by any means..

I'll try that out and get back...

Thanks Again,

Anju

• ###### Re: Adding expression after totals

Hi Anju,

You can do Max of year month condition using set analysis,that will solve your problem.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Tom,

I added some expressions using set analysis in the chart..

But its not giving me the desired results.

I came up with this for labels:

=sum({\$<Year = {\$(=max(Year(BATCH_MONTH)))}, Month = {\$(=max(Month(BATCH_MONTH)))}>} BATCH_MONTH)

and this for the value:

=sum({\$<Year = {\$(=max(Year(BATCH_MONTH)))}, Month = {\$(=max(Month(BATCH_MONTH)))}>} FPR_BRL_AMOUNT)

Firstly, only a single column is created...and not the dynamic number of columns as expected..

I will need a little help here..

Thanks again,

Anju

• ###### Re: Adding expression after totals

Hi Anju,

Can you share your application.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Anju,

Try to extract year and month from BATCH_MONTH into separate columns.

Then include the exression as YEAR={\$(=max(YEAR))}.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Tom,

I added two columns, one for year and one for month...But, even here, the expression is returning invalid data...Again, how do I combine them so that I have

columns labelled 'Jan 2012', 'Feb 2012'.....'Jan 2013',...'feb 2013'...etc...and also they should hold corresponding 'FPR_BRL_AMOUNT' in the columns.

The columns I added are the last two in 'Sheet 1'

Thanks and Regards,

Anju

• ###### Re: Adding expression after totals

Hi Anju,

You can get idea from the below attached application.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Tom,

Thanks a lot for taking the time out to help me here.Appreciate it!

Regards,

Anju

• ###### Re: Adding expression after totals

Hi,

Is there any way I can get all data ,i.e, across all years at once without making yearwise selections?...

Thanks,

Anju

• ###### Re: Adding expression after totals

Hi Anju,

You can remove the condition Year={\$(=max(Year))}.This will give all data on monthly basis.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Tom,

But that would group data for all months...say, data for month of JAN for 2012 and 2013 into one column...I would want it under separate labels Jan 2012 and Jan 2013

-Anju

• ###### Re: Adding expression after totals

Hi Anju,

Can you create the required structure in an excel file and share it to me.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Tom,

I have attached the xls file.

Thanks,

Anju

• ###### Re: Adding expression after totals

Hi Anju,

We can do that, but the problem is that acccording to our format we have to limit the number of years upto certain level otheriwse it becomes too tedious.Is this your clients request? Or we can try do some other format.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Anju,

You can take help from the attached application.

Regards,

Tom

• ###### Re: Adding expression after totals

Hi Tom,

The number of years will be varying...Then, this solution will be infeasible, as you said.Is there any other method?

Thanks,

Anju

• ###### Re: Adding expression after totals

Hi All,

We finally decided to go with an old version, where Penetration will appear after every column. This is a slight drawback , but it satisfies other parameters, and also trying to do anything more might just complicate things.

I am attaching  two versions of the report so that it might help somebody facing similar issues.

And a big thanks to Tom!!

Thanks and Regards,

Anju