topic Re: Previous quarter Balance in Pivot in QlikView

Previous quarter Balance in Pivot

Wed, 26 Feb 2014 07:48:08 GMT

Thanks in advance

I have one report which has calculated dimension Q1 2013, Q2 2013, Q3 2013, Q4 2013, Q1 2014

& expression as below.

Previous Quarter Balance = Sum({<OrderDate = {">=$(=vPrevQtrStartDate) <=$(=vPrevQtrEndDate)"}>} Balance)

Current Quarter Balance = Sum(Balance) - This is fine

vPrevQtrStartDate= QuarterStart(Addmonths(OrderDate, - 3))

vPrevQtrEndDate= QuarterEnd(Addmonths(OrderDate, - 3))

I always get 0 for Previous Quarter Balance which is wrong In fact this should be Current Quarter Balance from previous column

Your help would be appreciated, Thanks

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 07:51:41 GMT

Hi Rajendra

I think it would be easier with Above() function

above([ total ] expression [ , offset [,n ]])

Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent

Lukasz

Re: Previous quarter Balance in Pivot

MK_QSL — Wed, 26 Feb 2014 07:54:13 GMT

Please check there OrderDate and both variable's return should be in same format..

Re: Previous quarter Balance in Pivot

sundarakumar — Wed, 26 Feb 2014 07:54:55 GMT

Please check the values of vPreQtrStartDate and vPreQtrEndDate in a text box...

-sundar

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 07:56:59 GMT

Hi Lukasz

I appreciate your prompt reply.

As I am new in QV, could you elaborate more please

Thanking you

Raj

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 07:59:59 GMT

Good question Sundarakumar

If I use these variable in Text box, I don’t see any value, but if with formula I can see both correct date

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 08:00:09 GMT

It all depends on your data, but if you want to select the value for previous quarter you can use something like this in the chart/table:

Above(Sum(Balance))

Re: Previous quarter Balance in Pivot

sundarakumar — Wed, 26 Feb 2014 08:02:36 GMT

Sorry i dont understand, "if with formula i can see both correct date"

where are u seeing the correct date? Please explain.

-Sundar

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 08:02:59 GMT

I will try

Thanks a lot lot Lukasz

Re: Previous quarter Balance in Pivot

MK_QSL — Wed, 26 Feb 2014 08:03:10 GMT

Kindly upload your sample file or apps...

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 08:08:23 GMT

In Text box if I use

= vPrevQtrStartDate (this is variable I created the value is = QuarterStart(Addmonths(OrderDate, -3)) ) à I don’t see date but

If I use

= QuarterStart(Addmonths(OrderDate, -3)) than I can see the correct date

Same for End date

Thanks lot

Re: Previous quarter Balance in Pivot

MK_QSL — Wed, 26 Feb 2014 08:09:59 GMT

Looks like your OrderDate format is giving your a problem.

let us know the OrderDate format..

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 08:11:59 GMT

Hi Manish

This is very large file but I will create up loadable small file and forward to you soon

I appreciate your help

Raj Patel

Re: Previous quarter Balance in Pivot

sundarakumar — Wed, 26 Feb 2014 08:12:26 GMT

=$(vPrevQtrStartDate)

this should be in the text box

-Sundar

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 08:14:25 GMT

Mm/dd/yyyy

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 08:17:23 GMT

Unfortunately still don’t see, any other option please?

Thanks Sundarakumar

Re: Previous quarter Balance in Pivot

MK_QSL — Wed, 26 Feb 2014 09:11:08 GMT

If possible, please load your qvw or sample file..

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 10:42:00 GMT

Hi Manish

I have attached QV file.

Thanks for your help

Raj

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 10:42:36 GMT

Hi Sundarakumar

I have attached QV file

Thanks for your time and help

Raj

Re: Previous quarter Balance in Pivot

Wed, 26 Feb 2014 10:44:24 GMT

Hi Lukasz

I have attached QV file where Begin Balance are zero

Thanks for your time and efforts

Raj