topic Monthend function shows monthstart date...strange behaviour in legend in QlikView

Monthend function shows monthstart date...strange behaviour in legend

gauthamchilled — Wed, 22 Jul 2015 01:53:20 GMT

I used dimension scale in axes tab...I used monthend function but it shows as monthstart in chart..can anyone one look and help?

Re: Monthend function shows monthstart date...strange behaviour in legend

jagan — Wed, 22 Jul 2015 03:08:53 GMT

Hi Gautam,

You cannot control it from the Axes tab, the best option is to bring the new MonthEnd date field in the script and use it in the chart as dimension

Data:

LOAD

MonthEnd(DateField) AS MonthEndDate;

LOAD Repeat(chr(64 + Ceil(Rand() * 5)), 5) AS ID,

Date(Date('01/01/2010' + Ceil(Rand() * 730))) AS DateField,

Ceil(Rand() * 10000) AS Amt

AUTOGENERATE 100;

Hope this helps you.

Regards,

Jagan.

Re: Monthend function shows monthstart date...strange behaviour in legend

jagan — Wed, 22 Jul 2015 03:11:29 GMT

Also uncheck the Continuous option in Axes tab for Dimension.

Hope this helps you.

Regards,

Jagan.

Re: Monthend function shows monthstart date...strange behaviour in legend

gauthamchilled — Wed, 22 Jul 2015 03:16:01 GMT

Jagan,

Thanks for your reply.

But my requirement is I want to show all data of a month in the chart, if I use monthend..it will then pick the data for that monthend data..right?

I just want to limit the legends by showing only monthend with all the data.

Re: Monthend function shows monthstart date...strange behaviour in legend

settu_periasamy — Wed, 22 Jul 2015 03:17:44 GMT

Hi,

May you can create the Calculated dimension like

MonthEnd(DateField)

You can give the Set expression for limited display Month Data.

Re: Monthend function shows monthstart date...strange behaviour in legend

gauthamchilled — Wed, 22 Jul 2015 03:31:03 GMT

Settu,

I understand your file. But yours sums up the data per month. I want day wise data but show legends as monthend. something like the screenshot below.

Re: Monthend function shows monthstart date...strange behaviour in legend

gauthamchilled — Wed, 22 Jul 2015 03:39:00 GMT

I am not understanding why monthend function shows monthstart date in scale axis for dimension in my attached document.

Re: Monthend function shows monthstart date...strange behaviour in legend

jagan — Wed, 22 Jul 2015 03:57:47 GMT

Hi,

If you want to display only Monthend data then use below script

Data:

LOAD

If(MonthEnd(DateField) = DateField, 1, 0) AS MonthEndFlag;

LOAD Repeat(chr(64 + Ceil(Rand() * 5)), 5) AS ID,

Date(Date('01/01/2010' + Ceil(Rand() * 730))) AS DateField,

Ceil(Rand() * 10000) AS Amt

AUTOGENERATE 100;

Now in chart

Dimension: DateField

Expression: =Sum({<MonthEndFlag = {1}>}Amt)

Axes tab is display the range of values, it is not possible to change the dimension values.

Hope this helps you.

Regards,

Jagan.

Re: Monthend function shows monthstart date...strange behaviour in legend

preminqlik — Wed, 22 Jul 2015 04:01:37 GMT

i guess you require monthend date to select only monthend dates among all the dates

find the below code

Data:

LOAD Repeat(chr(64 + Ceil(Rand() * 5)), 5) AS ID,

Num(Date('01/01/2010' + Ceil(Rand() * 730))) AS DateField,

Ceil(Rand() * 10000) AS Amt

AUTOGENERATE 100;

MONTHENDDATESTABLE:

Load Distinct Monthend(DateField) as DateField,

Monthend(DateField) as Monthend

Resident Data;

Re: Monthend function shows monthstart date...strange behaviour in legend

gauthamchilled — Wed, 22 Jul 2015 04:03:17 GMT

Hi Jagan,

No, I want to show all the data in the chart, not just the monthend date data. but in the legen I want to show monthends with scales like I shown in the screenshot above.

Re: Monthend function shows monthstart date...strange behaviour in legend

jagan — Wed, 22 Jul 2015 04:09:44 GMT

Hi,

Hope attached file helps you.

Regards,

Jagan.

Re: Monthend function shows monthstart date...strange behaviour in legend

gauthamchilled — Wed, 22 Jul 2015 04:19:30 GMT

Even the monthname function not working in min scale or max scale expression. Any clue?

Re: Monthend function shows monthstart date...strange behaviour in legend

jagan — Wed, 22 Jul 2015 05:09:46 GMT

Hi,

Scale is to set the min and max value of the chart axes not for dimensional values. You can control the dimension values in expression.

Regards,

Jagan.

Re: Monthend function shows monthstart date...strange behaviour in legend

gauthamchilled — Wed, 22 Jul 2015 05:40:56 GMT

Hi Jagan,

If that is the case, how is it showing monthstart. Find the attached, I have create two charts to show the difference in the qlikview.

Re: Monthend function shows monthstart date...strange behaviour in legend

jagan — Wed, 22 Jul 2015 06:32:36 GMT

Remove the Continuous option, it will generate randomly but not as you wish. It is just to set the range in the axes not to control the dimension values.

I am not sure why you are looking for this option when there is other best options.

Regards,

jagan.