topic Re: Qlik calculation not working in QlikView

Qlik calculation not working

Tue, 19 Aug 2014 05:02:26 GMT

Hi,

The following calculation does not seem to work:

Sum({$<DATE={'$(=date(MAX(DATE)))'}>} Sales)

This has however worked in the past but am not sure whats wrong now.

Re: Qlik calculation not working

ecolomer — Tue, 19 Aug 2014 05:15:17 GMT

is possible that the format of DATE is wrong?

Re: Qlik calculation not working

ashfaq_haseeb — Tue, 19 Aug 2014 05:18:21 GMT

Hi,

Make sure you have proper masking.

Qlik is case sensitive.

Or post sample app to review.

Regards

ASHFAQ

Re: Qlik calculation not working

Tue, 19 Aug 2014 05:22:41 GMT

Hi,

Try With giving Date format

Re: Qlik calculation not working

jagan — Tue, 19 Aug 2014 05:25:07 GMT

Hi,

I think the issues is because of date format, the date format in DATE and Qlikview date format is different, to make sure check like this

Date(MAX(DATE)) and DATE field format are not same, you have to change the format by using

Date(DateFieldName, Format)

Example : Date(MAX(DATE), 'DD-MMM-YYYY')

Or simply when you are loading data transform the date like below

Data:

LOAD

Date(DATE) AS FormattedDate

FROM Source;

Now use this expression

Sum({$<FormattedDate={'$(=date(MAX(FormattedDate)))'}>} Sales)

Hope this helps you.

Regards,

Jagan.

Re: Qlik calculation not working

Tue, 19 Aug 2014 05:33:14 GMT

Enrique,

I had issues with dates as I had to extract data from database to flat files because of connection issues but that's a different story. The file extract produced the dates in 'MM/DD/YYYY'

I had handled this in the script to convert it to date using:

Date#( [File Date], 'MM/DD/YYYY')

My requirement was to display dates in international format hence I have also formatted the date:

Date(Date#( [File Date], 'MM/DD/YYYY'), 'YYYY-MM-DD')

Do you think this is causing issues?

Re: Qlik calculation not working

Tue, 19 Aug 2014 05:35:28 GMT

Jagan,

I have tried this but that doesn't seem to help

Re: Qlik calculation not working

ashfaq_haseeb — Tue, 19 Aug 2014 05:39:11 GMT

Hi,

Can you share your sample.

Regards

ASHFAQ

Re: Qlik calculation not working

jonathandienst — Tue, 19 Aug 2014 05:40:07 GMT

That looks correct, but format the Max date in the same way:

Sum({$<DATE={'$(=Date(Max(DATE), 'YYYY-MM-DD'))'}>} Sales)

HTH

Jonathan

Re: Qlik calculation not working

aveeeeeee7en — Tue, 19 Aug 2014 05:42:16 GMT

Try this:

Sum({$<DATE={'$(=MAX(DATE))'}>} Sales)

I thinks it's the Format Issue.

Regards

Aviral Nag

Re: Re: Qlik calculation not working

Tue, 19 Aug 2014 05:51:16 GMT

Ashfaq,

Please find the file attached. I have created a sample, could not sent the original

Re: Qlik calculation not working

ashfaq_haseeb — Tue, 19 Aug 2014 05:56:27 GMT

Hi,

Issue with your field name.

You use wrong field name

Regards

ASHFAQ

Re: Qlik calculation not working

ashfaq_haseeb — Tue, 19 Aug 2014 06:01:02 GMT

try this

=Sum({$<[Run Date]={'$(=date(max([Run Date]),'MM/DD/YYYY'))'}>} COUNT_PASS)

Regards

ASHFAQ

Re: Re: Re: Qlik calculation not working

aveeeeeee7en — Tue, 19 Aug 2014 06:01:22 GMT

I have seen you QVW. There is format problem.

I've create an variable for Run_Date and change its format to number.

See the Attachment.

Re: Qlik calculation not working

tresB — Tue, 19 Aug 2014 06:03:04 GMT

The working expression is:

=Sum({$<[Run Date]={"$(=date(MAX([Run Date]),'MM/DD/YYYY'))"}>} COUNT_PASS)

Re: Re: Qlik calculation not working

Tue, 19 Aug 2014 06:03:52 GMT

Ashfaq,

I do have a field called [Cound Pass] this was a copy paste error while creating the sample app.

Re: Re: Qlik calculation not working

ashfaq_haseeb — Tue, 19 Aug 2014 06:04:36 GMT

Hi,

I have attached working qvw file.

=Sum({$<[Run Date]={'$(=date(max([Run Date]),'MM/DD/YYYY'))'}>} COUNT_PASS)

Regards

ASHFAQ

Re: Re: Qlik calculation not working

Tue, 19 Aug 2014 06:31:36 GMT

Thanks for all the help guys! I really appreciate it... This is the best community ever ...

The thing that appears to be working is: Sum({$<[Run Date]={'$(=date(MAX([Run Date])))'}>}[Count Pass])

Which is kind of same as what I posted originally but in the meantime, I have changed the global date and time setting in the script and that appears to have resolved it. The date and timestamp format was mentioned differently and possibly that was causing issues.

Re: Qlik calculation not working

ashfaq_haseeb — Tue, 19 Aug 2014 06:35:48 GMT

Hi,

Can you close this thread by selecting appropriate answer.

Regards

ASHFAQ