Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

sebastian_tunne
New Contributor II

Problem with Date and AddDate

Hi everyone,

I have been having a problem with a formula in set analysis:

sum(aggr(

if([Payment Type ID] = 1

  ,sum({< [Calendar Year Month ID] =  {'$(=max([Calendar Year Month ID]))'}, [Payment Type ID] = {1}, [Calendar Date]=  {'<$(=date(today(0)-4))'}>}  _f_OK_POSITIVE_TRX)

  ,sum({< [Calendar Year Month ID] =  {'$(=max([Calendar Year Month ID])-1)'},[Payment Type ID] = {2},[Calendar Date]=  {'<$(=date(AddMonths(today(0)-4,-1))'}>}  _f_OK_POSITIVE_TRX)

  )

,[Merchant ID], [Payment Type ID],[Calendar Date]))

For some reason this part:

=date(AddMonths(today()-4,-1))

Works in a text box, but when I use it the set analysis it doesn't. Is there anything I'm missing?

Labels (2)
1 Solution

Accepted Solutions
sebastian_tunne
New Contributor II

Re: Problem with Date and AddDate

Hi everyone thanks for your help. I can't beleive I was banging my head for so long. It was just an extra parenthesis.

This worked:

[Calendar Date] = {'<$(=date(AddMonths(today(0)-4,-1)))'}

5 Replies
MVP
MVP

Re: Problem with Date and AddDate

That's hard to answer with the information provided. In general

- Check that the dollar sign expansion returns a date value with the same format as the field you are modifying ([Calendar Date]).

- If you remove the expression label in a straight table chart, you can hover over the label and see the expanded expression, to check that all dollar sign expansions work as intended

- Check that there are values for your field modifier search to return (i.e. Calendar Dates before the expanded date).

- Also check that potential user selections or other field modifier don't render the set incompatible (e.g. check the field modifications for Calendar Year Month ID is compatible to your Calendar Date selection and all possible user selections in calendar fields.

As you see, multiple possible causes why the expression might not work. It's often much easier to help if you could post  a small sample QVW.

gabsus04
Valued Contributor II

Re: Problem with Date and AddDate

Hi,

Replace this $(=date(AddMonths(today(0)-4,-1)) with

    = AddMonths(Date(today(0))-4,-1)

miguelbraga
Valued Contributor III

Re: Problem with Date and AddDate

Where you have this:

{'<$(=date(AddMonths(today(0)-4,-1))'}


Change to this:

{'<$(=date(AddMonths(today()-4,-1))'}

If don't work, report and/or elaborate your question better.

Regards,

MB

sebastian_tunne
New Contributor II

Re: Problem with Date and AddDate

Hi everyone thanks for your help. I can't beleive I was banging my head for so long. It was just an extra parenthesis.

This worked:

[Calendar Date] = {'<$(=date(AddMonths(today(0)-4,-1)))'}

miguelbraga
Valued Contributor III

Re: Problem with Date and AddDate

Sometimes the smallest rock makes the door impossible to open .

Continue with your good work and have a nice job.

MB