topic Re: expression help in QlikView

expression help

Thu, 04 Sep 2014 09:23:53 GMT

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

Re: expression help

vijay_iitkgp — Thu, 04 Sep 2014 09:27:24 GMT

You Can use 'Q' &Ceil(Month([Date Abandoned)/3) AS Quarter

Re: expression help

engishfaque — Thu, 04 Sep 2014 09:31:18 GMT

Dear Chris,

Try this,

'Q' & Ceil(Month(TO_DATE)/3) AS CalendarQuarter,

Kind regards,

Ishfaque Ahmed

Re: expression help

ashfaq_haseeb — Thu, 04 Sep 2014 09:33:43 GMT

Hi,

Try below

'Q'&ceil(month([Date Abandoned])/3) AS Quarter

Regards

ASHFAQ

Re: expression help

Thu, 04 Sep 2014 09:35:03 GMT

Hi,

Do I put this expression in the chart properties or in script?

Re: expression help

Thu, 04 Sep 2014 09:36:13 GMT

Hi all,

Tried this but get the message thAt says Garbage after expression. "AS"

Re: expression help

ashfaq_haseeb — Thu, 04 Sep 2014 09:36:33 GMT

Script, Make sure to comment previous expression.

Regards

ASHFAQ

Re: expression help

ashfaq_haseeb — Thu, 04 Sep 2014 09:42:01 GMT

Hi,

Did you try my expression in the script?

'Q'&ceil(month([Date Abandoned])/3) AS Quarter

Make sure to comment previous expression.

Regards

ASHFAQ

Re: expression help

Thu, 04 Sep 2014 09:47:53 GMT

Hi,

Yes same error:

'Q'&ceil(month([Date Abandoned])/3) AS Quarter - its says garbage after expression: "AS"

Chris

Re: expression help

ashfaq_haseeb — Thu, 04 Sep 2014 09:51:55 GMT

did you comment your previous quarter calculation,

Can you post your script.

may be , required

'Q'&ceil(month([Date Abandoned])/3) AS Quarter,

Regards

ASHFAQ

Re: expression help

PrashantSangle — Thu, 04 Sep 2014 09:58:03 GMT

Hi,

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

In script or in chart??

Regards

Re: expression help

Anonymous — Thu, 04 Sep 2014 10:04:07 GMT

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?

Re: expression help

Thu, 04 Sep 2014 10:04:25 GMT

Hi Ashfaq,

The script is one the orginal post

Chris

Re: expression help

Thu, 04 Sep 2014 10:10:13 GMT

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

Re: expression help

aveeeeeee7en — Thu, 04 Sep 2014 10:14:50 GMT

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

Re: expression help

PrashantSangle — Thu, 04 Sep 2014 10:16:28 GMT

Hi,

Try this,

LOAD [Place Ref]

    '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,