BAR CHART

[#1]

I want to display three months data based on selected month

for example :

i have two input box one is current selected year and another current selected month those two are variables

if i select jan 2014 it should display oct 2013 nov 2013 dec 2013

if i select april  2014 it should display jan 2014 feb 2014 mar 2014

i am getting one value only i am not getting  three months

please help me

Thanks

Sivaram

[jagan]

Hi Sivaram,

Modify your script to get the date field using below script

LOAD

*,

MonthName(MakeDate(SHIP_YEAR, Num(SHIP_MONTH))) AS MonthYear,

MakeDate(SHIP_YEAR, Num(SHIP_MONTH)) AS SHIP_DATE;

LOAD 

     SHIP_MONTH,

     TRANSPORT_MODE,

     SHIP_YEAR,

     SHIP_NO

FROM

(qvd);

Now use MonthYear as dimension and use below expressions

For AIR

=Count({<TRANSPORT_MODE={'AIR'}, SHIP_DATE={'>=$(=MonthStart(MakeDate(APPAC_SelectedYear, APPAC_SelectedMonth), -3))<=$(=MonthEnd(MakeDate(APPAC_SelectedYear, APPAC_SelectedMonth), -1))'}>} SHIP_NO)

For SEA

=Count({<TRANSPORT_MODE={'SEA'}, SHIP_DATE={'>=$(=MonthStart(MakeDate(APPAC_SelectedYear, APPAC_SelectedMonth), -3))<=$(=MonthEnd(MakeDate(APPAC_SelectedYear, APPAC_SelectedMonth), -1))'}>} SHIP_NO)

Hope this helps you.

Regards,

Jagan.

[#3]

Dear Jagan

Thank u very much

i am having SHIP_DATE date field also can u please send me qvw file that is created

i had added SHIP_DATE now plz check it

i am not getting data what u changes plz share qvw file

Thanks

sivaram

[jagan]

Hi,

Remove Timestamp from Date field using below script and see whether the given expressions are working

LOAD 

     SHIP_MONTH,

     TRANSPORT_MODE,

     SHIP_YEAR,

     Date(SHIP_DATE) AS SHIP_DATE,

     SHIP_NO

FROM

(qvd);

Regards,

Jagan.

[jagan]

Hi Sivaram,

Please find attached file for solution.

Regards,

Jagan.

[#6]

Hi jagan mohan

every time i want to show three months data

i want to show if is select 1 it should show december 2013 data november 2013 october 2013 .

in ur example if is select 2 it is showing jan data

filtering show take place current month and current year

please help me in this dear urgent

Thanks

Sivaram

[jagan]

Hi,

The expressions are correct, you dont have data for that months.

Regards,

Jagan.

[#8 · Accepted solution]

Based on our last discussion , here is your solution to FILL THE GAPS with zero values in YEARS/MONTHS. read the comments on the script on how I achieved this. NOTICE that in the attached file the 2013-Nov and 2013-Dec doesn't contain data, because you don't have data on those months. notice the little  0 on top of the date, this can be changed in properties->presentations->Zero on Bars  to not display it.

[#9]

And in the case YOU don't want to show the current selected month. then use the following expression :

ie for SEA:

replace this:

=COUNT({1 <TRANSPORT_MODE={'SEA'},YEAR_MONTH_SERIAL ={'>=$(=only(YEAR_MONTH_SERIAL)-3)<=$(=only(YEAR_MONTH_SERIAL))'}>} SHIP_NO)

for this:

=COUNT({1 <TRANSPORT_MODE={'SEA'},YEAR_MONTH_SERIAL ={'>=$(=only(YEAR_MONTH_SERIAL)-3)<$(=only(YEAR_MONTH_SERIAL))'}>} SHIP_NO)

and so on for the other expressions.

[#10]

BUT JUST IN case , this is a modified version that doesn't include the current selected year/month.