Error in Quarter wise data selection

[deepakqlikview_123]

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,

[SergeyMak · Accepted solution]

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

[deepakqlikview_123]

Hi,

Is there any other way for this.

Thanks

[SergeyMak]

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

[deepakqlikview_123]

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,

[SergeyMak]

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

[SergeyMak]

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

[deepakqlikview_123]

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

[SergeyMak]

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

There is no sense to connect calendar table by DealerId

[deepakqlikview_123]

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