topic Re: Adding expression after totals in QlikView

Adding expression after totals

Thu, 18 Oct 2012 06:51:53 GMT

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.

http://community.qlik.com/message/270290#270290..

Please scroll towards the end to see my questions.

Thanks ,

Anju

Re: Adding expression after totals

Thu, 18 Oct 2012 08:40:20 GMT

Cud u post ur sample qvw ?

Regards

Re: Adding expression after totals

Thu, 18 Oct 2012 08:54:57 GMT

Hi qlik_techie,

Thanks a lot for looking into this!

-Anju

Re: Adding expression after totals

thomas_skariah — Thu, 18 Oct 2012 09:30:05 GMT

Hi Anju,

Find the attached application.

Try out. Modify it as per your requirement.

Regards,

Tom

Re: Adding expression after totals

Thu, 18 Oct 2012 11:22:51 GMT

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

thomas_skariah — Thu, 18 Oct 2012 12:47:39 GMT

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

Fri, 19 Oct 2012 07:23:10 GMT

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

thomas_skariah — Fri, 19 Oct 2012 07:27:32 GMT

Hi Anju,

Can you share your application.

Regards,

Tom

Re: Adding expression after totals

thomas_skariah — Fri, 19 Oct 2012 07:30:06 GMT

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

Fri, 19 Oct 2012 09:31:43 GMT

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

thomas_skariah — Sat, 20 Oct 2012 05:07:35 GMT

Hi Anju,

You can get idea from the below attached application.

Regards,

Tom

Re: Adding expression after totals

Mon, 22 Oct 2012 04:29:37 GMT

Hi Tom,

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

Regards,

Anju

Re: Adding expression after totals

Thu, 25 Oct 2012 08:03:38 GMT

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

thomas_skariah — Thu, 25 Oct 2012 08:21:42 GMT

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

Thu, 25 Oct 2012 08:26:40 GMT

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

thomas_skariah — Thu, 25 Oct 2012 08:39:12 GMT

Hi Anju,

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

Regards,

Tom

Re: Adding expression after totals

Thu, 25 Oct 2012 09:29:36 GMT

Hi Tom,

I have attached the xls file.

Thanks,

Anju

Re: Adding expression after totals

thomas_skariah — Thu, 25 Oct 2012 09:48:24 GMT

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

thomas_skariah — Thu, 25 Oct 2012 10:04:32 GMT

Hi Anju,

You can take help from the attached application.

Regards,

Tom

Re: Adding expression after totals

Thu, 25 Oct 2012 10:19:07 GMT

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