Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

bullish35
Contributor

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

Something wrong in formula?

=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
Contributor

Something wrong in formula?

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

Something wrong in formula?

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.

MVP
MVP

Something wrong in formula?

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

Something wrong in formula?

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

MVP
MVP

Something wrong in formula?

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

Something wrong in formula?

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

MVP
MVP

Something wrong in formula?

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

Something wrong in formula?

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
Community Browser