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: 
SailajaPulugurtha
Contributor II
Contributor II

Date formatting working as expression but not as dimension in straight table

I have a straight table with columns as Employee, country and ten different date type columns. I have taken Employee and country as dimensions and transaction date as calculated dimension as below

if(country='US',Date([transaction date],'MM/DD/YYYY',Date([transaction date],'DD/MM/YYYY') 

This is not giving me correct results, but when the same is converted as expression the same is working fine.

But due to the number of expressions, dashboard is taking so much time to load and sometimes getting error as "out of object memory" as there is huge data that is being considered in the straight table

Any suggestions on how to best handle this?

9 Replies
MayilVahanan

HI @SailajaPulugurtha 

You can convert date format in the script and use it in the front end.
But, why you want different date format for each country. If you are using the date in set analysis, how you define the date format in it??
Or create one field with date format for each country, and use in the expression like below

Load * Inline
[
Country, DateFormat1
US, DD/MM/YYYY
IND, MM/DD/YYYY
];

=Date([transaction date], DateFormat1)

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
SailajaPulugurtha
Contributor II
Contributor II
Author

Thanks for your response. The business requirement is to have multiple date formats for various countries. I am using the below one in the calculated dimension

if(country='US',Date([transaction date],'MM/DD/YYYY',Date([transaction date],'DD/MM/YYYY')

Can you please give example how we can use this is in the set analysis

MayilVahanan

Hi @SailajaPulugurtha 

Create one inline table with country n date format and use in the  front end.

Load * Inline
[
Country, DateFormat1
US, DD/MM/YYYY
IND, MM/DD/YYYY
];

Front end

=Date([transaction date], DateFormat1)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
SailajaPulugurtha
Contributor II
Contributor II
Author

Thanks for your response. I have made the changes accordingly. I am able to see the format correctly but the date refuses to change when the formatted date is used to change the format. Attaching ss for reference

inline_table:
Load * inline
[COUNTRY,Dateformat1
US,'MM/DD/YYYY'
AU,'DD/MM/YYYY'
UK,'DD/MM/YYYY'
]
;

MayilVahanan

Hi @SailajaPulugurtha 

There is no single quotes required. 
Can you please send sample file..

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
SailajaPulugurtha
Contributor II
Contributor II
Author

Hi without quotes also not working. 

SailajaPulugurtha
Contributor II
Contributor II
Author

Please find the  sample.xls file.

sunsun566
Contributor III
Contributor III

Hi

I think you can check if the association has "$ Syn".
Because your script is not complicated, it should not be slow.

The following is my script, hope it helps 🙂

Pick(Match(country, 'US', 'AU', 'UK'),  Date([transaction date], 'MM/DD/YYYY'), Date([transaction date], 'DD/MM/YYYY'), Date([transaction date], 'DD/MM/YYYY'))

MayilVahanan

Hi @SailajaPulugurtha 

PFA

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.