expression help

[#1]

Hi,

Looking for help please:

We have an app with the following script- the most important part of this is the If statement that turn a calender date in the data into Quarters. From this I want to create both a straight table and a chart that shows 'description' numbers by quarter. The chart is really easy and works; however, the straight table is just populating the same figures each quarter. I need help with what expression to put in the staright table to show figures each quarter. I can't attach application so refer to word atatchement with screen shots.

Any help would be really appreciated.

LOAD [Place Ref]

  [Tenancy Ref],
     [Address Concat],
     [Job No],
     Contract,
     [Short Description],
     [Date Reported],
     [Target Date],
     [Initial Priority],
     [Latest Priority],
     [Date Abandoned],
     If( Month([Date Abandoned]) >=4,  year([Date Abandoned])&'/'&  (year([Date Abandoned])+1), (year([Date Abandoned])-1)&'/'&year([Date Abandoned])) as FiscalYear,
     If(month([Date Abandoned])<=3,'Q4', if(month([Date Abandoned])>=4 and month([Date Abandoned])<=6,'Q1', if(month([Date Abandoned])>=7 and month([Date Abandoned])<=9, 'Q2', 'Q3'))) as Quarter,
     Description,
     [Reported to Target days],
     [Reported to Abandoned days],
     [Target to Abandoned days],
     Month,

Year

Chris

[PrashantSangle]

Hi,

Just wanted to know where you are putting suggested expression,??

In script or in chart??

Regards

[Anonymous]

obviously None of the replys noticed that you have a fiscal year starting in april

their solution with ceil will work but you have to adjust it to your fiscal year

but I would like to know what is Display. you say, for each quarter the same result. can you specify more in detail

or give example?

[#4]

Hi Ashfaq,

The script is one the orginal post

Chris

[#5]

Hi Rudolf,

Can you advise with fiscal year in mind what expression would be?

Currently the table shows the same number of jobs (description) for each quarter. for example 'diplicate order' is a cancellation reason; however, the number of times this is applicable/selected will change each quarter- this is what I would like table to replicate in table.

Chris

[aveeeeeee7en]

Hi

Try this in your script:

YearName(YourDateField,0,4) AS CommonYearName, // This will create your Fiscal Year Starting from April to March

'Q'&ceil(Num(Month(AddMonths(YourDateField,-3)))/3) AS CommonQuarter   // This will create your Quarters working according to your Fiscal Year:

1. Apr-May-Jun - Quarter 1

2. Jul-Aug-Sept - Quarter 2

3. Oct-Nov-Dec - Quarter 3

4. Jan-Feb-Mar - Quarter 4

Create these in Backend Scripts:

YearName(YourDateField,0,4) AS CommonYearName,

'Q'&ceil(Num(Month(AddMonths(YourDateField,-3)))/3) AS CommonQuarter

Hope that helps you.

Regards

Aviral Nag

[PrashantSangle]

Hi,

Try this,

LOAD [Place Ref]

  [Tenancy Ref],
     [Address Concat],
     [Job No],
     Contract,
     [Short Description],
     [Date Reported],
     [Target Date],
     [Initial Priority],
     [Latest Priority],
     [Date Abandoned],
     If( Month([Date Abandoned]) >=4,  year([Date Abandoned])&'/'&  (year([Date Abandoned])+1), (year([Date Abandoned])-1)&'/'&year([Date Abandoned])) as FiscalYear,
     If(month([Date Abandoned])<=3,'Q4', if(month([Date Abandoned])>=4 and month([Date Abandoned])<=6,'Q1', if(month([Date Abandoned])>=7 and month([Date Abandoned])<=9, 'Q2', 'Q3'))) as Quarter,

    'Q' & If(Ceil(Month(TO_DATE)/3)>1,Ceil(Month(TO_DATE)/3)-1,Ceil(Month(TO_DATE)/3)+3) AS fiscalQuarter,
     Description,
     [Reported to Target days],
     [Reported to Abandoned days],
     [Target to Abandoned days],
     Month,

Year

Hope this will help.

Regards,