topic Pivot table in QlikView

Pivot table

Fri, 24 May 2013 06:13:10 GMT

Hello,

I am using QV 11.

I have Pivot table with 5 dimensions one of which is Month that I have pivoted.

I have written expression as

(sum({< Month = {'>=$(=(Max(Month))) <=$(=(Max(Month)+2))'} >} SalesValue)/ $(varConversionRate)

which gives me 3 months data.

I also have the curreny field with values as LCY( Local currency) and USD. and the currency Rate.

When I select LCY, it should show actual values from the DB.

When I select USD, it should apply the currency Rate and then display the values for the 3 months.

But when I select USD, the USD rate is getting applied only for current month and not for other 2 months.

Please can one help me on this.

Re: Pivot table

vikasmahajan — Fri, 24 May 2013 06:39:52 GMT

Hii Apurva

Please create sample application and attach

Vikas

Re: Pivot table

Fri, 24 May 2013 07:07:42 GMT

Hi ,

You please try this

=(Sum({< Month = {'>=$(=MonthStart(Max(Month))) <=$(=MonthEnd(Max(Month),2))'} >} SalesValue)/ $(varConversionRate)

I hope this is helpful to you

Re: Pivot table

Mon, 27 May 2013 04:10:43 GMT

Hi amuthabharathi,

I tried the above... but still it is not working.

The USD rate is getting applied only for the current selected month.

Re: Pivot table

Wed, 29 May 2013 09:03:26 GMT

any help please

Re: Pivot table

jonathandienst — Wed, 29 May 2013 09:09:07 GMT

Please show the expression contained in varConversionRate.

Regards

Jonathan

Re: Pivot table

Wed, 29 May 2013 09:53:44 GMT

if(Currency = 'USD', varCurrencyRate, 1)

Re: Pivot table

jonathandienst — Wed, 29 May 2013 11:31:43 GMT

Is varCurrencyRate a variable with a fixed rate amount, or is it another expression?

Re: Pivot table

Wed, 29 May 2013 11:40:11 GMT

it is not a fixed rate. I am picking it from url and storing in qvd day wise and then as per year/mnth/day selections, getting the rate and applying it when USD is selected. similarly I have done in other sales report and it is working. Only difference was that the other sales report is a monthly report and this is a report with 3 months data.

Re: Pivot table

jonathandienst — Wed, 29 May 2013 11:42:47 GMT

That implies that varCurrencyRate is a field that is associated in some way with the date of the sale. It might help if you could post your model or a sample. You can scramble the data if the information is sensitive.