topic Re: Label as Expression - can't calculate in QlikView

Label as Expression - can't calculate

Fri, 20 Sep 2013 14:08:08 GMT

I have a problem with Label Expression. If I want to calculate difference between dates in new expression, it will not allowed me.

I have two Labels with expression which shows me current 2013 month and the same month 2012:

Sep 2013 Label:

=Date(YearMonthProper,'MMM YY')

Sep 2012 Label:

=date(max(addmonths(YearMonthProper,-12)),'MMM YY')

If you do = [Sep 12]-[Sep 13] it works.

but in this occasion you can't use it as you have to put:

[Date(YearMonthProper,'MMM YY')]-[date(max(addmonths(YearMonthProper,-12)),'MMM YY') ]

*they not getting blue [.....]

Any resolution?

Thanks,

Re: Label as Expression - can't calculate

adamwilson — Fri, 20 Sep 2013 14:47:39 GMT

IF you want the label to display [Sep 12]-[Sep 13] then this should work:

='['& Date(YearMonthProper,'MMM YY') &']-['& date(max(addmonths(YearMonthProper,-12)),'MMM YY') &']'

Re: Label as Expression - can't calculate

Fri, 20 Sep 2013 15:04:40 GMT

I'm sorry, maybe I wasn't clear enough. I have a pivot table. I want compare Sales 12 with sales 13 against each month. So for example Sep 2012 against Sep 2013. I don't want to change labels in the next month so I added expressions in labels. Everything looks like that:

Sales September 2013:

Label: =Date(YearMonthProper,'MMM YY') which displays "Sep 13"

Definition:

({<Year={$(=max(Year))}, Month={"<=$(=max({<Year={$(=max(Year))}>}Month))>$(=max({<Year={$(=max(Year))}>}Month)-1)"},[Invoice Order Tag]={'Invoiced'}>}[Net sales value])

Sales September 2012:

Label: =date(max(addmonths(YearMonthProper,-12)),'MMM YY') which displays "Sep 12"

Definition:

({<Year={*},Month={*},YearMonthProper={"$(=date(max(addmonths(YearMonthProper,-12)),'dd/MMM/YYYY'))"},[Invoice Order Tag]={'Invoiced'}>}[Net sales value]

Sep 12 and Sep 13 display on the pivot table but the fields name in the expression are full expressions. I can't calculate difference in sales between those two dates as just doesn't work like I said before.

Any ideas?

Thanks.

Re: Label as Expression - can't calculate

Carlos_Reyes — Fri, 20 Sep 2013 15:15:54 GMT

So, and I wonder if I get you right, you want to calculate the difference between the two expressions in a new (third) expression and you can't reference the previous two because their labels are formulas?

If that's the problem you can reference the expressions by their coulmn number.

For instance, if Sep 13 is the first expression you could do the next formula/expression in the third (new) expression:

column(1)-column(2)

The disadvantage is that if you enable users to move the columns, this approach will fail whenever the order of the columns is changed.