Misbehaviour in the chart while using user interactive input box

[#1]

Hi All,

I have some issues while using the user interactive chart using the input box. In my document there are two images. In first i want to show how many manufacturer have the sale value more than what entered in the input box. And in the second i want to show how many items have the sale value more than what entered in the input box.

When i enter any value in the input box it shows correct value in both the chart. But when there is no value in the input box. It shows the equal numbers in both the chart. As per the data manufacturer data should have been lesser when compared to the itemname. Don't know where i am going wrong. Check the image for better understanding. Hope some one will help me out soon.

[#2]

Hi Rikab,

The real issue is with using "Year" as the counting dimention. Your chart dimention is from April 2006 until March 2007 for "2006-2007" chart dimention and you are counting "Year" , obviously it will give count as "2" because there are 2 years within this chart dimention - "2006" and "2007". You may have to create another Year field using the yearname(Date,0,4) while loading the data to get the correct count and use that dimention for counting the number of "mfacname".

Hope this will solve the issue.

Thanks,

Sajeevan

[#3]

Sajeevan wrote:
The real issue is with using "Year" as the counting dimention. Your chart dimention is from April 2006 until March 2007 for "2006-2007" chart dimention and you are counting "Year" , obviously it will give count as "2" because there are 2 years within this chart dimention - "2006" and "2007". You may have to create another Year field using the yearname(Date,0,4) while loading the data to get the correct count and use that dimention for counting the number of "mfacname". <div></div>

Yes I agree with your point. I was actually trying like this but it was not working.

count(if(aggr(sum(SalValue), yearname(Date,0,4), mfacname)>if(len(iDiffValue)=0,-1,iDiffValue),1))
count(if(aggr(sum(SalValue), yearname(Date,0,4), itemname)>if(len(iDiffValue)=0,-1,iDiffValue),1))

So as per your request I tried to load the financial year at the time of loading the data itself. But the strange thing are happening. Calender was the only table which is there in the script but Calender-1 got created by itself. So got confused how it got created? Will it make performance issue?

See the attached script and the screen shot for reference.

Thanks and Regards,

Rikab

[#4]

Hi Rikab,

The financial year you created outside sales data is not linking with anything. You need to create the FinancialYear variable while loading the sales data so that you get the FinancialYear as a dimention to get the count of manufaturer etc. Please do so and try.

Thanks,

Sajeevan

[#5]

Sajeevan wrote:
The financial year you created outside sales data is not linking with anything. You need to create the FinancialYear variable while loading the sales data so that you get the FinancialYear as a dimention to get the count of manufaturer etc. Please do so and try. <div></div>

When I tried to load within sales table itself I am getting following error.

sales:
SQL select stk_sales.c_ac_code as acc,
d_date as Date,Year(d_date) as Year,Month(d_date) as Month,YearName(Date,0,4) as [FinancialYear],stk_sales.c_item_code as itemcode,
n_purqty as PurQty,n_pur_Sch_qty as PurSch,n_purvalue as PurValue,
n_salqty as SalQty,n_sal_sch_qty as SalSch,n_salvalue as SalValue,
n_crqty as RtnQty,n_cr_sch_qty as RtnSch,n_crvalue as RtnValue,
n_clqty as StkQty,n_clvalue as StkValue from stk_sales,item_mst, mfac_mst
where stk_sales.c_item_code=item_mst.c_item_code and item_mst.c_mfac_code=mfac_mst.c_mfac_code;

Thanks and Regards,

Rikab

[#6]

Hi Rikab,

The error is because you are using a non-existing variable in the table. Please use "d_date" instead of "Date" i.e. "YearName(d_date,0,4) as [FinancialYear]". Hope this will work.

Thanks,

Sajeevan

[#7]

Sajeevan wrote:
The error is because you are using a non-existing variable in the table. Please use "d_date" instead of "Date" i.e. "YearName(d_date,0,4) as [FinancialYear]". Hope this will work. <div></div>

No I already tried that it is not working.

sales:
SQL select stk_sales.c_ac_code as acc,Year(d_date) as Year,Month(d_date) as Month,d_date as Date,YearName(d_date,0,4) as [FinancialYear],stk_sales.c_item_code as itemcode,
n_purqty as PurQty,n_pur_Sch_qty as PurSch,n_purvalue as PurValue,
n_salqty as SalQty,n_sal_sch_qty as SalSch,n_salvalue as SalValue,
n_crqty as RtnQty,n_cr_sch_qty as RtnSch,n_crvalue as RtnValue,
n_clqty as StkQty,n_clvalue as StkValue from stk_sales,item_mst, mfac_mst
where (stk_sales.c_item_code=item_mst.c_item_code and item_mst.c_mfac_code=mfac_mst.c_mfac_code) and Year(d_date) in('2006','2007');
//where stk_sales.c_item_code=item_mst.c_item_code and item_mst.c_mfac_code=mfac_mst.c_mfac_code;

Error:

Thanks and Regards,

Rikab

[#8]

Hi Rikab,

I don't know why it is giving an error. In that case, remove that part from the sales data loading and have it as a separate table outside like below.

CalYear:

LOAD d_date, yearname(d_date,0,4) as FinancialYear resident sales;

This will link the d_date in Sales table with FinancialYear in CalYear table so that you can count the mfacname. Let me know the outcome.

Thanks,

Sajeevan

[#9]

Sajeevan wrote:
I don't know why it is giving an error. In that case, remove that part from the sales data loading and have it as a separate table outside like below.
CalYear:
LOAD d_date, yearname(d_date,0,4) as FinancialYear resident sales;
This will link the d_date in Sales table with FinancialYear in CalYear table so that you can count the mfacname. Let me know the outcome. <div></div>

That is what I did in previously.

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for Data=False;Tag with column collation when possible=False];

sales:
SQL select stk_sales.c_ac_code as acc,Year(d_date) as Year,Month(d_date) as Month,d_date as Date,stk_sales.c_item_code as itemcode,
n_purqty as PurQty,n_pur_Sch_qty as PurSch,n_purvalue as PurValue,
n_salqty as SalQty,n_sal_sch_qty as SalSch,n_salvalue as SalValue,
n_crqty as RtnQty,n_cr_sch_qty as RtnSch,n_crvalue as RtnValue,
n_clqty as StkQty,n_clvalue as StkValue from stk_sales,item_mst, mfac_mst
where (stk_sales.c_item_code=item_mst.c_item_code and item_mst.c_mfac_code=mfac_mst.c_mfac_code) and Year(d_date) in('2006','2007');
//where stk_sales.c_item_code=item_mst.c_item_code and item_mst.c_mfac_code=mfac_mst.c_mfac_code;

t1:
Load Distinct Date
Resident sales;

Calender:
Load
Date,
YearName(Date,0,4) as [FinancialYear]
Resident t1;

Drop Table t1;

Thanks and Regards,

Rikab

[#10]

Hi. It dosen't look like Calender1 is a new table but your inline table that has changed name ?

[#11]

DK_ASN wrote:
Hi. It dosen't look like Calender1 is a new table but your inline table that has changed name ?<div></div>

Whatever how the name got changed? If you can notice in my script I have not given any table name as Calender-1, may be Calender is present.

Thanks and Regards,

Rikab