topic Error in Quarter wise data selection in QlikView

Error in Quarter wise data selection

deepakqlikview_123 — Wed, 21 May 2014 05:11:16 GMT

Hi All,

Please find attached QVW file.

My quarter wise data selection is not working properly.

April data is displayed in 3 quarters.Moreover there is also requirement to show data date wise Please suggest.

Thanks,

Re: Error in Quarter wise data selection

SergeyMak — Wed, 21 May 2014 05:19:01 GMT

I think the best way will be to build a separate Calendar.

The example here http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

and use dates and quarters from this table

Re: Error in Quarter wise data selection

deepakqlikview_123 — Wed, 21 May 2014 05:49:05 GMT

Hi,

Is there any other way for this.

Thanks

Re: Error in Quarter wise data selection

SergeyMak — Wed, 21 May 2014 05:57:31 GMT

yes. you can, But it's always better to have separate calendar.

Your problem is that you calculate month and quarter from different dates

month(if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-', date(DealerToDealerInvoiceDate,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY'))) as Retail_Month,

'Q' & Ceil(Month(date(DealerToDealerInvoiceDate,'DD-MM-YYYY'))/3) as Retail_Quarter,

you should select one date and calculate month from DealerToDealerInvoiceDate or

quarter from

if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-', date(DealerToDealerInvoiceDate,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY')))

Re: Error in Quarter wise data selection

deepakqlikview_123 — Wed, 21 May 2014 06:17:07 GMT

Hi Seregy,

Thanks for response.

It is working as per expectation.

I am using all date fields from previous table by using resident load for calendar creation.

Can you please tell me when to use system field and when to use previously loaded tables field for calendar creation,and which will be better to use in which case.

Thanks,

Re: Error in Quarter wise data selection

SergeyMak — Wed, 21 May 2014 06:28:13 GMT

It's better to have a calendar, because Date is dimension and it will consume less space and memory and more expandable (you can simpler add a new description of a day like holiday or end of month etc) if you store Date, Month, Year, Quarter, Fiscal Year, Fiscal Month, Holiday flag etc.... once.

The second reason is case when you don't have all the dates in your transactional data:

For example you have 01.01.2014 and 03.01.2014, but you need to show missing values e.g. show 02.01.2014 with no value.

But in your case the problem is you use different dates for calculating month a quarter to describe a day in one row

Re: Error in Quarter wise data selection

SergeyMak — Wed, 21 May 2014 06:40:51 GMT

Unexpectedly I found a string

if(FuelType='Diesel' or FuelType='DIESEL','Diesel',If(FuelType='LPG','LPG',If(FuelType='CNG','CNG')))as FuelType,

It's much faster to use only

UPPER(FuelType) AS FuelType, if upper DIESEL is ok

or use only

if(FuelType='DIESEL','Diesel',FuelType) AS FuelType

Re: Re: Error in Quarter wise data selection

deepakqlikview_123 — Wed, 21 May 2014 07:21:16 GMT

Hi ,

For showing month,quarter,year,Day properly I have created new calendar table using resident data(On retail table)

I have left joined calendar with organizationmaster,but it seems results are not displaying correctly.

Please suggest.

Thanks

Re: Re: Error in Quarter wise data selection

SergeyMak — Wed, 21 May 2014 07:28:48 GMT

you should connect Calendar by Dealer Invoice Date, not by DealerID

There is no sense to connect calendar table by DealerId

Re: Re: Error in Quarter wise data selection

deepakqlikview_123 — Wed, 21 May 2014 07:37:19 GMT

Thanks Segey,

It is working fine now.

But quarter is still showing as Q,Q1,Q2.

Can u please suggest how to make correction on this display.

Thanks

Re: Re: Error in Quarter wise data selection

SergeyMak — Wed, 21 May 2014 07:46:48 GMT

First of all you need to change your script

Calendar:

load

DealerID

resident OrganisationMaster;

Left Join

LOAD

DealerID,

You should change to

Calendar:

LOAD DISTINCT

your further script

because you don't need DealedId in this table and you need only unique records of dates.

Your "Q" appeared because for some records you don't have DealerInvoiceDate. You just have to decide what you need to do in this case.

But you didn't generate the calendar, you just move dates to another table

please read carefully http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

and try to get the powerful calendar

Re: Re: Error in Quarter wise data selection

deepakqlikview_123 — Wed, 21 May 2014 08:03:08 GMT

Hi Seregy,

I do not want to display only 'Q' But still I am not able to understand what action I need to in this case.

Can you please suggest.

Thanks,

Deepak

Re: Re: Error in Quarter wise data selection

SergeyMak — Wed, 21 May 2014 08:11:38 GMT

If you create a normal calendar it will disappear.

But if you don't want just add after Resident Retail

WHERE isNull(DealerInvoiceDate)=0;

But in any case you will not be able to see your data if DealerInvoiceDate without value.

You should have filled KEY field

Re: Error in Quarter wise data selection

deepakqlikview_123 — Wed, 21 May 2014 11:29:21 GMT

In My ccode if Diesel or SIESEL then i need to show only 1 field as Diesel,

and if LPG then LPG and if CNG then CNG

Actually I am drill down to detail level so use this code in script

if(FuelType='Diesel' or FuelType='DIESEL','Diesel',If(FuelType='LPG','LPG',If(FuelType='CNG','CNG')))as FuelType,

Re: Error in Quarter wise data selection

deepakqlikview_123 — Fri, 23 May 2014 05:39:19 GMT

Hi Seregy,

I have used

month(if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-', date(DealerToDealerInvoiceDate,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY'))) as Retail_Month,

Condition because in my table where updated on in null then I have to use DealerToDealerInvoiceDate otherwise i have to use UpdatedOn.

If this is not right idea for creation of month date then,

by using only DealerInvoiceDate will it be show proper results after generating calendar.

Please suggest.

Thanks,

Deepak

Re: Error in Quarter wise data selection

SergeyMak — Fri, 23 May 2014 05:43:02 GMT

You can add new calculated field

if(isnull(date(UpdatedOn)) or date(UpdatedOn)='-', date(DealerToDealerInvoiceDate,'DD-MM-YYYY'), date(UpdatedOn,'DD-MM-YYYY')) AS Calculated.Date

build calendar based on this new date and connect to this date Calendar table

Re: Error in Quarter wise data selection

deepakqlikview_123 — Fri, 23 May 2014 07:27:57 GMT

Hi Seregy,

I want to remove all record starting from DI99 can you please tell me what will be condition for this.

Thanks,

Deepak