38 Replies Latest reply: Apr 29, 2010 3:00 AM by At titude RSS

    Misbehaviour in the chart while using user interactive input box

      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.

       

      error loading image

       

      error loading image

       

        • Misbehaviour in the chart while using user interactive input box

          Hi All,

          This is what i have used in the Manufacturer expression

           

          count(if(aggr(sum(SalValue), Year, mfacname)>iDiffValue,1))


          and this is what i have used in the Itemname chart expression

           

          count(if(aggr(sum(SalValue), Year, itemname)>iDiffValue,1))


          Let me know where i am going wrong.

          • Misbehaviour in the chart while using user interactive input box
            Michael Anthony

            Believe the problem is your expression and what it is comparing against when iDiffValue is blank - or actually is Null. QV doesn't like comparing a Aggr(Sum...) against a Null value so returns true for every record which is why get same value.

            Try wrapping iDiffValue in a RangeSum(iDiffValue). this will convert it to 0 if it's Null and your expressions should work as expected.

            • Misbehaviour in the chart while using user interactive input box
              Annette Søgaard

              Hi. You wrote "There is one problem I am facing in the interactive chart. The problem is that as per the straight table there are only one manufacturer in the year 2006-2007 and it should be only one only. But whereas in the interactive chart it shows "2" manufacturers in the year 2006-2007."

              If you look at your straigth table it shows 1 for 2006 and 1 for 2007. In your interactive chart these are summed and show 1+1 =2.

               

                • Misbehaviour in the chart while using user interactive input box

                   


                  DK_ASN wrote:
                  If you look at your straigth table it shows 1 for 2006 and 1 for 2007. In your interactive chart these are summed and show 1+1 =2. <div></div>


                  Yes, I agree with your point.But the thing is that I am counting the number of mfacname from April,2006 to March,2007 using this expression

                  =yearname(Date,0,4)
                  but it is still showing "2" .

                  Do you mean to say that if I use the above expression it will sum both the year? Please check and let me know

                  Do you also mean to say that wherever I am using the expression the values that are calculated or wrong?

                  Thanks and Regards,

                  Rikab

                   

                    • Misbehaviour in the chart while using user interactive input box
                      Sajeevan Govindan

                      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

                       

                       

                        • Misbehaviour in the chart while using user interactive input box

                           


                          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

                           

                           

                            • Misbehaviour in the chart while using user interactive input box
                              Sajeevan Govindan

                              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

                               

                                • Misbehaviour in the chart while using user interactive input box

                                   


                                  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

                                    • Misbehaviour in the chart while using user interactive input box
                                      Sajeevan Govindan

                                      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

                                       

                                        • Misbehaviour in the chart while using user interactive input box

                                           


                                          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

                                           

                                            • Misbehaviour in the chart while using user interactive input box
                                              Sajeevan Govindan

                                              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

                                               

                                                • Misbehaviour in the chart while using user interactive input box

                                                   


                                                  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

                                                   

                                • Misbehaviour in the chart while using user interactive input box
                                  Annette Søgaard

                                  Hi again. The sinner is your "Year" in your expression

                                  count(if(aggr(sum(SalValue), Year, mfacname)>if(len(iDiffValue)=0,-1,iDiffValue),1))

                                  Remove the Year, and it shows one.

                                   

                                    • Misbehaviour in the chart while using user interactive input box

                                       


                                      DK_ASN wrote:
                                      Hi again. The sinner is your "Year" in your expression
                                      count(if(aggr(sum(SalValue), Year, mfacname)>if(len(iDiffValue)=0,-1,iDiffValue),1))
                                      Remove the Year, and it shows one. <div></div>


                                      After removing the year it shows that "No data to display" Please check whether i have done the right change as said by you

                                      Modified Expression:

                                       


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


                                       

                                       

                                       

                                    • Misbehaviour in the chart while using user interactive input box
                                      Annette Søgaard

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