Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date Island Period as Chart Dimension

Hi Guys

I need to chart the SalesValue as a Line Chart using the Date Island’s DI_PERIOD (yyyyMM) is the x-axis dimension for this chart.

I have tried this but my Sales graph is level all the way across.

Sum({$<Date={"<=$(=Max(DI_Date))"},Type={‘I’}>} Value)   

Type depicts an Invoice in my FACT table

I used the Date Island's DI_Period (yyyyMM) as my x-axis dimension in the Chart.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

As far as your original post is concerned, you cannot use set analysis in reference to a dimension in a chart as the set analysis is applied once for the chart, before the dimensions are constructed. Is there a specific reason you are using a date island for the dimension?

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
adnan_rafiq
Partner - Creator II
Partner - Creator II

Bruce,

Apparently it seems that you have some data modeling problem.

here are couple of tips: 1. Make sure table which has DI_Period is linked to the table which contains Value field.

Apparently there is no association among these two table.

2. if due to certain limitations you are not able to connect them, then create a field on monthyear(Date) from the date.

Regards

Not applicable
Author

Hi Adnan

Ok, so I have in the load statement for my FACT table done the following:

LOAD

     Date(QM_ADD_DATE,'yyyyMMdd')             as Date,

     Date(QM_ADD_DATE,'yyyyMM')                as Period etc etc

But when I use Period as my x-axis dimension, it leave gaps for each day.  I only need sales by Period, not Date.

I cant seem to attach a image of the graph but hope you know what I mean?

Not applicable
Author

Hi Adnan

Ok, so I have in the load statement for my FACT table done the following:

LOAD

     Date(QM_ADD_DATE,'yyyyMMdd')             as Date,

     Date(QM_ADD_DATE,'yyyyMM')                as Period etc etc

But when I use Period as my x-axis dimension, it leave gaps for each day.  I only need sales by Period, not Date.

I cant seem to attach a image of the graph but hope you know what I mean?

jonathandienst
Partner - Champion III
Partner - Champion III

Bruce,

The Date() statement formats, but does not change the underlying value of the field. In your case, Date and Period contain exactly the same values, just formatted differently. So you have multiple (different) values for each period - one for each day. You can see that in a listbox and in your dimension.

You could try something like this to get one value per period:

     Date(MonthStart(QM_ADD_DATE), 'yyyyMM') as Period

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

As far as your original post is concerned, you cannot use set analysis in reference to a dimension in a chart as the set analysis is applied once for the chart, before the dimensions are constructed. Is there a specific reason you are using a date island for the dimension?

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein