topic Something wrong in formula? in QlikView

Something wrong in formula?

bullish35 — Thu, 28 Oct 2010 20:25:18 GMT

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!

Something wrong in formula?

Thu, 28 Oct 2010 20:27:36 GMT

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

Something wrong in formula?

bullish35 — Thu, 28 Oct 2010 20:49:40 GMT

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

Something wrong in formula?

Fri, 29 Oct 2010 19:53:49 GMT

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.

Something wrong in formula?

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 20:05:31 GMT

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!

Something wrong in formula?

Fri, 29 Oct 2010 20:23:55 GMT

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

Something wrong in formula?

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 20:30:27 GMT

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

Something wrong in formula?

Fri, 29 Oct 2010 20:43:00 GMT

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

Something wrong in formula?

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 20:47:25 GMT

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?

Something wrong in formula?

Fri, 29 Oct 2010 21:13:59 GMT

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

Something wrong in formula?

Miguel_Angel_Baeyens — Sat, 30 Oct 2010 07:31:38 GMT

Kim,

If MaxPlanAsOfDate has one only possible value, it makes sense to me that there is only one possible value that matches and so it will return data for those dates and not (because you are using set analysis to match only records with that date) the other dates.

If you want to sum regardless the selection done in ActualDate, use

Sum({< ActualDate = >} ActualData)

Hope that helps

Something wrong in formula?

Sat, 30 Oct 2010 21:16:15 GMT

Hi Miguel,

I changed all of my date data fields to have a Date ("MM/DD/YYYY") format. For example, I loaded ActualDate as:

LOAD Date

(RIGHT(Date, 2) & '/30/20' & LEFT(Date, 2), 'MM/DD/YYYY') as ActualDate

Then I used your formula: Sum({<ActualDate={"$(=Date(MaxPlanAsOfDate, 'MM/DD/YYYY'))"}>}ActualData)

Again, this sum only shows up if I choose 06/30/2010. If I choose any other dates, the sum is 0.

What did I do wrong here?

Thanks again,

-Kim

Something wrong in formula?

Sun, 31 Oct 2010 03:23:53 GMT

I may have been unclear before. I need to have the sum of ActualData at 06/30/2010 regardless of what ActualDate is selected (I should have the same number no matter what ActualDate is selected). Is there a way to do this in Qlikview?

Sum({< ActualDate = >} ActualData) will give me the sum of ActualData depends on what ActualDate is selected. If ActualDate = 09/30/2010 is selected, it'll give me the sum of data for 09/30/2010, not 06/30/2010.

num(Sum({$<ActualDate={'06/30/2010'}>}ActualData), '#,##0', '.' , ',') shows the right sum when ActualDate of 06/30/2010 is selected. If other dates are selected, I want to have the same sum too, but this sum is 0 when other dates are selected.

What did I do wrong here?

Thanks much,

-Kim

Something wrong in formula?

Mon, 01 Nov 2010 15:00:57 GMT

Can you repost your message? It is being cut off and I can't tell what the remaining text says.

Your second expression seems to suggest that the expression works when the date is selected. I'm guessing that when the data is selected, this expression will also give that answer:

num(Sum(ActualData), '#,##0', '.' , ',')

Meaning that your Set Analysis isn't doing anything in that case. Since you are overriding your ActualDate selection in the Set Analysis, that should ignore the selection on that field and instead follow your Set Modifier. That isn't working in your expression, because the date must be selected for it to work. That means that the Set Modifier is not working. It is probably a formatting issue.

Create a List Box for your ActualDate field. What is the format of those dates in the List Box? Is it M/D/YYYY, MM/DD/YYYY, YYYY-MM-DD, etc? That format is very important in getting this to work. If it is 6/30/2010, 06/30/2010 will not work.

You should also check out the following post for some tips on adding code to your posts: http://community.qlik.com/forums/t/15789.aspx