Jump to content


  • Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About sam81

  • Rank

Profile Information

  • Gender
    Not Telling
  1. sam81

    Using GetSummary

    I sorted the data based on Region...but I get blank..
  2. I have following data set Unique ID Product ID submode Region Sales 1 x x1 US 10 2 x x1 EU 20 3 x x2 EU 30 4 x x2 US 40 5 y y1 JPY 50 6 y y1 US 60 now i need to add the sales based on product and region level x US 50 x EU 50 and so on...................i know i can use sub summary reports but I need the valyes (50 and 50) in a field since i need to do further analysis of this field . I tried using getsummary(SummarySales,Region) but I got nothing. Not sure what I am missing.
  3. @Kris/David: I cannot use UniqueValues as I have FMP advanced but not Advanced Pro...not sure how i add custom functions in FMP advanced....please let me know if there is other way around @consultant: i tried your logic...but for some reason it is not working....not sure why..do i need to create a filed 'Types' with valuelist from MOdels:type before i used the calculation...... Thank you for all your help !!
  4. I am trying to concatenate unique child record for parent record...i was able to concatenate all child records for a given parent record using Substitute ( List ( child::record ) ; ¶ ; ", " )....but cannot figure out how do i remove the duplicate values for example: Car company has multiple models..but each model can only car, mid-suv, large-suv, truck etc.....now when i am populating information for Ford, i only want to have car, mid-suv and truck once not for all the model...and same for Hyundai can someone please guide about how to remove the extra child record during list thanks
  5. Hello Will, When i use your code in sql compiler it gives me correct results...but when i used in FMP it gives me error...not sure what the issue is ExecuteSQL( "SELECT SUM( sales ) FROM testsalesdata WHERE ( quarterID = '2014-q2' OR quarterID = '2014-q1' OR quarterID = '2013-q4' OR quarterID = '2013-q3' )"; "";"") Also, wanted to clarify that I am using it for "Specify calculation".......not sure what the error is for thank you Also, what to specify that I am using it in the 'Specify calc
  6. hello Will, for Sql query i want the query to reply the sum of last 4 quarters for each product..so result table should be product Sales number 1 180 (30+40+50+60) 2 420 (90+100+110+120)
  7. Hello 'Comment', i used filtered relationship/portal to display only sales from the last 4 quarters in a layout of Products....then i duplicated the portal and used "summary field" to find sum of last 4 quarters for the filetered portal [i did not knew FMP had this functionality...thanks a lot !] but problem lies that i cannot use this 'summary field' for further calculation....when i use this calculated field somewhere else it gives total different value ..how do i resolve this..or do i need scripting for this ?
  8. I cannot figure out how to sum values for last 4 quarters. FMP has function to SUM all values but not selected values..How do i sum the last 4 quarters for the given product... unique id product id quarter id sales 1 1 2013-q1 10 2 1 2013-q2 20 3 1 2013-q3 30 4 1 2013-q4 40 5 1 2014-q1 50 6 1 2014-q2 60 7 2 2013-q1 70 8 2 2013-q2 80 9 2 2013-q3 90 10 2 2013-q4 100 11 2 2014-q1 110 12 2 2014-q2 120 i created a ExecuteSQL query ExecuteSQL ( "SELECT SUM(TOP 4) IMS data::Quarter ID, IMS data::Units sold USA ORDER BY IMS data::Quarter ID DESC"; "¶" ;"" )
  9. this seemed to work...thank you eos !
  10. Hello Wim, thanks for reply. But x and y are in same table...not sure if you meant that...in short data is like this year unitid unit# packageid package# boxesid boxes# 2013 x11 2 x 5 X 17 2013 x12 3 2013 y11 6 2013 y12 6 y 12 2014 x11 2 x 5 15 2014 x12 4 2014 y11 5 y 10 2014 y12 5 there is a set relationship between x11/x12 to ->x and x/y to X .... Not sure if summary field will help in this scenario...i have tried SQL but setting up multiple loops has been complicated :'(
  11. I need to setup relationship where I can add up multiple number of units to give packages...and multiple packages to boxes all for a given year for eg. Years Units Packages Boxes 2013 x11 =2 x= 5 17 2013 x12 =3 2013 y11=6 2013 y12 =6 y=12 2014 x11 =2 x=5 15 2014 x12 =4 2014 y11=5 y=10 2014 y12 =5 I am not sure how to set up such complex relationships and 'sum' calculation. any suggestions will be appreciated !!

Important Information

By using this site, you agree to our Terms of Use.