topic Re: Date formatting working as expression but not as dimension in straight table in QlikView

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

SailajaPulugurtha — Fri, 15 Nov 2024 23:42:22 GMT

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?

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

MayilVahanan — Mon, 02 Nov 2020 04:10:21 GMT

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

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

SailajaPulugurtha — Mon, 02 Nov 2020 06:27:49 GMT

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

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

MayilVahanan — Mon, 02 Nov 2020 06:35:19 GMT

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)

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

SailajaPulugurtha — Mon, 02 Nov 2020 07:13:21 GMT

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'
];

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

MayilVahanan — Mon, 02 Nov 2020 08:04:28 GMT

Hi @SailajaPulugurtha

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

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

SailajaPulugurtha — Mon, 02 Nov 2020 08:14:20 GMT

Hi without quotes also not working.

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

SailajaPulugurtha — Mon, 02 Nov 2020 08:24:22 GMT

Please find the sample.xls file.

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

sunsun566 — Mon, 02 Nov 2020 08:45:06 GMT

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'))

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

MayilVahanan — Mon, 02 Nov 2020 09:50:01 GMT

Hi @SailajaPulugurtha

PFA