Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT

Date (Square) Problem

Hello,


Here, if i want to show date like a square b square like that, How to show that?

Capture.PNG

Like this,,


Let's Assume if date is 1-Aug-2016 Then i want to show like -- for that image,  1 is instead of a and st is instead of 2 -- 1st-Aug-2016("st" should be acting as square like image)


If 2nd Day -- nd

If 3rd Day -- rd

If 4th --- 30th Day -- th

If 31st Day -- st

Can i know how to achieve that? -- =Date(Now(),'DD' & '^rd' & ' ' & '-' & 'MMM-YYYY')

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
1 Solution

Accepted Solutions

Re: Date (Square) Problem

Hey Anil,

Sorry for my late reply. You can use below one.

Pick((Match(Right(Day(DateField),1),1,2,3) + Pick(Match(Day(DateField),11,12,13)+1,0,-1,-2,-3))+1,

Date(DateField,'DD' & Chr(7511)&Chr(688) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(738)&Chr(7511) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(8319)&Chr(7496) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(691)&Chr(7496) & ', MMM YYYY'),

) as FormattedDate

I have attached a sample file for your better understanding. If you still have performance issue, then try replacing day function (I am not sure about it but give a try) with SubField function in the above expression.

Untitled.jpg

View solution in original post

18 Replies
Partner
Partner

Re: Date (Square) Problem

Hi Anil,

I believe you would have to write an if statement identifying which ending to use and then use Chr() function with superscript characters. There is a listing of superscript characters here.

Using the Ord() function, you can find the corresponding character number and then use the following:

4ᵗʰ would then be =4&Chr(7511)&Chr(688)

Hope this helps.

Re: Date (Square) Problem

Kind of helpful this, Would you share the characters list for superscript like

A- Z & a-z like  Chr(7511)&Chr(688)

So, May i can look those aSAP

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Date (Square) Problem

Re: Date (Square) Problem

Tamil, These are sufficient for me, But this gives only Characters. But i need to get Super characters like above mentioned

Chr(7511) -- "t"

Chr(688) -- "h"


These above are gives upper characters. I am expecting this

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Date (Square) Problem

Re: Date (Square) Problem

Bit difficult to me, As of now can you help me on this with character numbers

Lower Letters for of these, Can you send Chr values for these. So that, we can discuss another part.

d    -

n    -

r     - 

s    -

t     -

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Date (Square) Problem

It's quite easy Anil. Just use search option (Ctrl + F) and paste the character. You will see the matching letters get highlighted in yellow color. Pick the required one.

I am helping you out with this part.

d    - Chr(7496)

n    - Chr(8319)

r     - Chr(7523)

s    - Chr(738)

t     - Chr(7511)

Note: I have updated the link.

Re: Date (Square) Problem

Haha, Yes. But i am using Mobile as of now. Anyhow, I got finally like below

Capture.PNG

Thanks pogrebnoymike & tamilarasu


Now, Coming to my Rek - I've used below to get some kind of idea. Now, i want to know is there any way to do similar


My Date Format is -- DD-MM-YYYY


If(Left(DateField,2) = '01', Date(DateField,'DD') & Chr(738)&Chr(7511) & ', ' & Date(DateField,'MMM YYYY'),

If(Left(DateField,2) = '02', Date(DateField,'DD') & Chr(8319)&Chr(7496) & ', '  & Date(DateField,'MMM YYYY'),

If(Left(DateField,2) = '03', Date(DateField,'DD') & Chr(691)&Chr(7496) & ', '  & Date(DateField,'MMM YYYY'),

Date(DateField,'DD') & Chr(7511)&Chr(688) & ', '  & Date(DateField,'MMM YYYY'))))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Date (Square) Problem

Anil,

May be this?

If(Match(Day(DateField),11,12,13) Or (Not Match(Right(Day(DateField),1),1,2,3)), Date(DateField,'DD' & Chr(7511)&Chr(688) & ', MMM YYYY'),

If(Right(Day(DateField),1)=1, Date(DateField,'DD' & Chr(738)&Chr(7511) & ', MMM YYYY'),

If(Right(Day(DateField),1)=2, Date(DateField,'DD' & Chr(8319)&Chr(7496) & ', MMM YYYY'),

If(Right(Day(DateField),1)=3, Date(DateField,'DD' & Chr(691)&Chr(7496) & ', MMM YYYY'),

)))) as FormattedDate

Capture.PNG