Date & Text Issue on Pivot Table

[kfahri342]

Hi,

I have the following underlying table

Type A
Dec-17
Mar-18
TBC
Issue Raised

When I put this in QlikView, it looks like:

Type A
43070
43160
TBC
Issue Raised

I have tried the following formula to get the date in "MMM-YY" format; =Date([Type A],'MMM-YY') .

But it will then get rid of my Text. Which again is not what I am looking to achieve. Please also note, the Text may vary and change over time, so a nested IF function such as, =IF([Type A]=TBC,'TBC',(Date([Type A],'MMM-YY'))) , is not ideal.

Any suggestions please.

Many Thanks

[kaushiknsolanki]

Hi,

Try this.

While loading the data load in text only. Like below.

     Load Text(Field) as Field

     From xyz;

Regards,

Kaushik Solanki

[kfahri342]

Thanks for the suggestion. But I ideally want to keep them in date format. As I sort these dates from earliest to latest. If I change these into dates, they will be ordered alphabetically.

[kaushiknsolanki · Accepted solution]

Hi,

Then you can try this.

Load If(Isnum(Field),Date(Field,'MMM-YY'),Field) as Field

From xyz;

Regards,

Kaushik Solanki

[kfahri342]

Works perfectly. Thank You

[#6]

Hi, I am new to qlikview and this community, 

I have a question about your reply concerning the Pivot table date and text.

In the answer you provided to Kozan Fahri, does the word Field refer to the col name or field from the date? And what would xyz be representing? 

Thank you in advance!

debra

[kaushiknsolanki]

Hi,

Yes the Field is the Column name from the table. And XYZ is the table from which this field belongs.

The given is the code for the scripting, but you can use the below code as calculated dimension in your pivot table.

If(Isnum(Field),Date(Field,'MMM-YY'),Field)

Regards,

Kaushik Solanki