topic Re: Dates in a chart in QlikView

Dates in a chart

Tue, 27 Oct 2015 17:46:31 GMT

I have a chart that has date (field name as 'Month') in x-axis and amount in y-axis. I have two questions regarding these

1) How can I add $ sign to the amount?

2) The date format in my excel file is May-15 for example but in the chart it shows as 5 digit random number. How can I fix that?

Thank you!

Re: Dates in a chart

miguelbraga — Tue, 27 Oct 2015 17:50:55 GMT

Hi Seona,

The first question is easy to answer, go to Number in your chart and check Money type.

The second question you should make a change in your script as follows:

Date(yourDateField, 'MMM-YY') as yourDateField

This way your Date value will come with the right format. Hope it helps

Regards,

Re: Dates in a chart

sunny_talwar — Tue, 27 Oct 2015 17:51:37 GMT

For 1:

Try to wrap your expression with Num() function like this may be -> Num(YourExpression, '$#,##0.0')

For 2:

Use one of these as your dimension:

MonthName(Date)
Date(MonthStart(Date), 'MMM-YY')

Re: Dates in a chart

robert_mika — Tue, 27 Oct 2015 17:54:51 GMT

In your chart

1) Go to Properties-->Number->Change format to Money

2) Use Date function in script level

Date(Your Field,'MMM-YY') as MonthYear

Re: Dates in a chart

Tue, 27 Oct 2015 18:56:14 GMT

Hi Sunny,

Could you elaborate more on how to implement these? Should I put them under the expressions tab?

Re: Dates in a chart

sunny_talwar — Tue, 27 Oct 2015 19:02:19 GMT

1 would be in the expression

2 would be the dimension

Re: Dates in a chart

Tue, 27 Oct 2015 19:51:12 GMT

So I was able to convert the string into date format. Now I need to turn the date 10/21/15 to 10/15 (Month Year). I used the suggested expressions in my dimensions but the chart just sums up all the data into one bar. What am I doing wrong?

Re: Dates in a chart

robert_mika — Tue, 27 Oct 2015 20:20:49 GMT

The format you need is

'MM-YY'

Not sure about the summing - could you post your app here?

Re: Dates in a chart

MarcoWedel — Tue, 27 Oct 2015 22:58:29 GMT

Hi,

one solution could be:

table1:

LOAD Date(MonthStart(Date),'MMM-YY') as Month,

Date(MonthStart(Date),'MM/YY') as Month2,

Date(Date) as Date,

Money(Amount) as Amount

Inline [

Date, Amount

42005, 1234

42006, 1235

42036, 2345

42038, 2346

42064, 3456

42067, 3457

42095, 4567

42099, 4568

42125, 5678

42130, 5679

42156, 6789

42162, 6790

42186, 7890

42193, 7891

42217, 8901

42225, 8902

42248, 9012

42257, 9013

42278, 1357

42288, 1358

42309, 3579

42320, 3580

42339, 5791

42351, 5792

];

hope this helps

regards

Marco