topic To show all dates in chart in QlikView

To show all dates in chart

agni_gold — Wed, 04 Nov 2015 17:01:04 GMT

Hi All ,

I have a situation

I have 2 Fact tables and one calender

Now my both facts are linked with each other by 2 concatinated fields (Field1 + Date)

and one fact is linked with calender by his date field.

Now when i am making staight table with dimension as Month and Day and 2 expresions are sum of Amount from 1st fact and sum of amount of 2nd Fact.

Then it is only showing common day which is common in both fact .

But i want all the days in a month , for those value is not present it will show 0 and rest values.

Re: To show all dates in chart

Clever_Anjos — Wed, 04 Nov 2015 17:04:47 GMT

Unchecking "Supress Zero-Values" should be enough

Re: To show all dates in chart

agni_gold — Wed, 04 Nov 2015 17:07:15 GMT

Yes it has already been unchecked

Re: To show all dates in chart

agni_gold — Wed, 04 Nov 2015 17:25:45 GMT

Here is my sample application please help

Re: To show all dates in chart

agni_gold — Wed, 04 Nov 2015 17:30:14 GMT

Sorry forgot to attch application

Re: To show all dates in chart

Clever_Anjos — Wed, 04 Nov 2015 17:33:19 GMT

As I can see

You have dates from 1-Jan-14 through 15-Jan-14
You have ammount for all days (Fact1 + Fact2)

Am I missing something?

Would you mind sharing your DataModel?

Re: To show all dates in chart

Clever_Anjos — Wed, 04 Nov 2015 17:37:54 GMT

Please check if this model does what you need

Fact1:
LOAD Date1 as Date, 
     Entity1, 
     Amount1
FROM
[https://community.qlik.com/servlet/JiveServlet/download/905407-195237/Data.xlsx]
(ooxml, embedded labels, table is Fact1);


Fact2:
LOAD Date2 as Date, 
     Entity2, 
     Amount2
FROM
[https://community.qlik.com/servlet/JiveServlet/download/905407-195237/Data.xlsx]
(ooxml, embedded labels, table is Fact2);


LOAD Month, 
     Day, 
     Date
FROM
[https://community.qlik.com/servlet/JiveServlet/download/905407-195237/Data.xlsx]
(ooxml, embedded labels, table is Calender);

Re: To show all dates in chart

agni_gold — Wed, 04 Nov 2015 17:42:58 GMT

but i need to make join between entity also

Re: To show all dates in chart

agni_gold — Wed, 04 Nov 2015 17:44:51 GMT

user want entity filter he wants to see entity wise data from fact1 and fact 2

Re: To show all dates in chart

Clever_Anjos — Wed, 04 Nov 2015 18:01:20 GMT

Would you mind elaborating a bit more your business requirement?

Re: To show all dates in chart

agni_gold — Wed, 04 Nov 2015 18:12:36 GMT

My application will be sort based on entity only , and days should not filter when i select entity , suppose in my calender 15 days are present then in my chart days will not filter.

this is my basic requirement

Re: To show all dates in chart

Wed, 04 Nov 2015 18:30:50 GMT

This is what you can use

Fact1:
LOAD Date1 AS Date,
Entity1,
Date1&Entity1 AS Link,
Amount1
FROM

(ooxml, embedded labels, table is Fact1);

outer join
LOAD
Date2 as Date,
Entity2,
Date2&Entity2 AS Link,
Amount2
FROM

(ooxml, embedded labels, table is Fact2);

Calender:
LOAD Month,
Day,
Date
FROM

(ooxml, embedded labels, table is Calender);

Re: To show all dates in chart

agni_gold — Wed, 04 Nov 2015 18:42:56 GMT

same case heappen , when i select entity1 it will sort the days.

Re: To show all dates in chart

Wed, 04 Nov 2015 18:52:23 GMT

Use this and then you can use Flag to distinguish from where your entity is coming:

Fact1:
LOAD Date1 AS Date,
Entity1 as Entity,
Amount1,
'Fact1' as Flag
FROM

(ooxml, embedded labels, table is Fact1);

outer join
LOAD
Date2 as Date,
Entity2 as Entity,
Amount2,
'Fact2' as Flag
FROM

(ooxml, embedded labels, table is Fact2);

Calender:
LOAD Month,
Day,
Date
FROM

(ooxml, embedded labels, table is Calender);

Re: To show all dates in chart

Wed, 04 Nov 2015 18:54:30 GMT

You can as well say concatenate in place of outer join both will work