topic Re: Set Analysis Giving Wrong Result in QlikView

Set Analysis Giving Wrong Result

Fri, 29 Nov 2013 06:08:23 GMT

Hi Experts,

I Have filters called region, sub-region, business-group,Quarter and Month-year

My requirement is to display the current month and prior 5 months of the sales.

for example i have a sales in the year 2012 up to Jan-2013 (13 months sales )

in this case, to display the sales of Rolling 6 months ( Jan 13, Dec 12, Nov 12, Oct 12, Sep 12, Aug 12 ) , in case if the current month is no data means we should display the null value

for that month as well as the other months data also to display in line chart

This is the expression i has written in the expression tab..

"=Sum({$<MonthYear = {">=$(=Date(addmonths(Max(MonthYear), -5), 'MMM YY')) <=$(=Date(addmonths(Max(MonthYear), 0), 'MMM YY'))"},REGION={'ASIA'}>} SALES)/1000000 "

But the line chart is displaying rolling 3 months sales data, Please find the below image...

Any Help would be Greatly Appreciated.

Thanks in Advance

Regards

Venkat

Re: Set Analysis Giving Wrong Result

tresB — Fri, 29 Nov 2013 06:15:00 GMT

Make sure that the 'Show All Values' is checked in the dimension tab, and supression option in the presentation tab is unchecked. May be data is not there in those months and it is getting supressed.

Re: Set Analysis Giving Wrong Result

Fri, 29 Nov 2013 06:26:59 GMT

Hi Venkat

Try this hope it will help

"=Sum({$<Datefield= {'>=$(Vrolling6)<=$(vMaxDate)'},REGION={'ASIA'}>} SALES)/1000000 "

Variables:

Vrolling3: =Date(MonthStart(vMaxDate,-5),'DD/MM/YYYY')

vMaxDate: =Date(max(CommonDateField),'DD/MM/YYYY')

Regards

Nirmal raj

Re: Set Analysis Giving Wrong Result

Fri, 29 Nov 2013 06:43:07 GMT

Hi Nirmal,

I tried it as you said, but it wont work

Re: Set Analysis Giving Wrong Result

Fri, 29 Nov 2013 06:44:04 GMT

will you upload your sample application....

Re: Set Analysis Giving Wrong Result

Fri, 29 Nov 2013 06:44:35 GMT

As you said, All options are fine in the report..But still i have the same problem

Re: Set Analysis Giving Wrong Result

Fri, 29 Nov 2013 08:10:48 GMT

Hi,

check this attach file. try to do like this. In this I wrote separate expression for every month.

Hope this helps you.

Regards,

Ashutosh