topic Re: How to get the Date in QlikView

How to get the Date

Fri, 30 Aug 2013 09:01:13 GMT

Hi All,

I have the following table

YearMonth , Revenue, Category

2013-1-1, 100,A

2013-2-1,200,B

If I use one pivot table to show the data, use YearMonth and Category as Dimension, and Sum(Revenue) as the Metric in expression.

What I want it, I want to get the Current row's YearMonth in the Expression.

Take example, the first row is 2013-1-1 in Dimension, which function I can use to capture that the current YearMonth is 2013-1-1?

Thanks.

tresB — Fri, 30 Aug 2013 09:06:04 GMT

You can directly write : =YearMonth in the expression.

Fri, 30 Aug 2013 09:32:42 GMT

But when I use this one, it does not work:

=Sum({<YearMonth={'=$(=Only(YearMonth))'}>} Revenue) YearMonth is the dimension

CELAMBARASAN — Fri, 30 Aug 2013 09:36:21 GMT

Hi,

You can use YearMonth directly.

Also it depends on what expression your using Aggr or Sum.

Can you explain little bit more about the purpose of it.

CELAMBARASAN — Fri, 30 Aug 2013 09:37:45 GMT

Why your going for this kind of expression?

Sum(Revenue) will give you the Revenue for the YearMonth value in the dimension.

Fri, 30 Aug 2013 09:48:04 GMT

Because I want to re-write the YearMonth

Take an example:

If YearMonth =2013-3, I want to show the revenue 2013-2 and 2013-3, the latest 2 months revenue in YearMonth 2013-3

CELAMBARASAN — Fri, 30 Aug 2013 09:51:15 GMT

You need something like this

Sum(Revenue)+Alt(Above(Sum(Revenue)),0)

Fri, 30 Aug 2013 09:58:52 GMT

Can we use the Set Analysis to achieve it by change the YearMonth?

CELAMBARASAN — Fri, 30 Aug 2013 10:08:47 GMT

No.Set analysis will work based on dimension.

er_mohit — Fri, 30 Aug 2013 10:17:59 GMT

Try this

sum(if(YearMonth=Aggr(NODISTINCT only(YearMonth),Category),Revenue))