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: 
bullish35
Creator II
Creator II

Something wrong in formula?

Can someone tell me if they see something wrong with this formula?

Sum({$<ActualDate={'$(=Date(MaxPlanAsOfDate))'}>}ActualData)

When I hardcode the time period using the formula below, it works.

Sum({$<ActualDate={'2010-06'}>} ActualData)

Much thanks!

13 Replies
Not applicable

=Date(MaxPlanAsOfDate) is probably returning a full date and your working expression only has a partial. You probably need:

Sum({$<ActualDate={'$(=Date(MaxPlanAsOfDate, "YYYY-MM"))'}>}ActualData)


When using a dollar sign expansion, it is a good idea to create a straight table and add your expression without giving it a label. When the chart is rendered, the label will be your expression with the dollar sign expansion evaluated. For your example, you will see the value will be a different format than what you need based on your working expression.

bullish35
Creator II
Creator II
Author

Thank you. This didn't work - yet! However, I am trying to solve this for someone that I work with and may not understand the issue entirely. I am going to ask for additional clarification. It could be that you are correct. Thanks again! Ellen

Not applicable

1) When I had Sum({$<ActualDate={'2010-06'}>} ActualData) and '2010-06' is chosen, then the sum shows up. If I choose any other date, the sum is 0.

Then I used Sum({1<ActualDate={'2010-06'}>} ActualData). This sum seems to work when I choose dates from 2010-06 and later, but when I select 2010-03, for example, the sum is wrong. How do I get the sam sum no matter what date I select here?

2) Note that the dates mentioned above are string data type, not date data type.

I have left(Year(MaxPlanAsOfDate), 4) & '-06' shows up in the table to be '2010-06', but Sum({1<ActualDate={left(Year(MaxPlanAsOfDate), 4) & '-06' }>} ActualData) = 0 no matter what date I choose.

I even let a variable vDate = left(Year(MaxPlanAsOfDate), 4) & '-06' , then use
Sum({1<ActualDate={'$(vDate)'}>}ActualData), but it doesn't work.

What am I missing?

3) How do we deal with "out of virtual memory " error message?

Thank you for your help.

Miguel_Angel_Baeyens

Hi Ellen,

It seems to be a question of formatting. Try:

Sum({$<ActualDate={"$(=Date(MaxPlanAsOfDate, 'YYYY-MM'))"}>}ActualData)


Take care when comparing dates, as both field and variable/expression must be in the same format.

Hope that helps!

Not applicable

Thanks Miguel.

I tried your suggested solution

Sum({$<ActualDate={"$(=Date(MaxPlanAsOfDate, 'YYYY-MM'))"}>}ActualData)

and the sum shows up when '2010-06' is selected, but if I select other dates, the sum is zero.

Then I tried, Sum({1<ActualDate={"$(=Date(MaxPlanAsOfDate, 'YYYY-MM'))"}>}ActualData). It works for dates after 2010-06. If I choose 2009-09, the sum doesn't come out right. What am I missing?

Thanks again.

-Kim

Miguel_Angel_Baeyens

Hello Kim,

Which different date formats do you store in MaxPlanAsOfDate field (or variable)?

Using "1" before the set modifier ignores the current selection and includes all possible values, so that's why you will see a lot of rows.

Regards

Not applicable

Hi Miguel,

In Excel, PlanAsOfDate column has values like 06/30/2010 with format mm/yyyy. Then I use the following load statement:

Load

date(max([PlanAsOfDate])) as MaxPlanAsOfDate

ActualDate is actually a TEXT (not date) column in Excel file with format yyyy-mm. It has values like "2010-06", "2010-09", and so on.

Thanks again,

-Kim

Miguel_Angel_Baeyens

I see. So if you are loading max() then you group by some field? Have you checked with a listbox how many different records MaxPlanAsOfDate has?

Not applicable

Hi Miguel,

PlanAsOfDate has multiple values, but MaxPlanAsOfDate has only 1 value: 06/30/2010. Somehow,

Sum({$<ActualDate={"$(=Date(MaxPlanAsOfDate, 'YYYY-MM'))"}>}ActualData)
only works when ActualDate of '2010-06' is selected. When I select other dates, the sum is 0.
Is there any way the sum is the same no matter what ActualDate I choose?
Thanks much,
-Kim