topic TimePeriods as Dimetions in QlikView

TimePeriods as Dimetions

xtrimf — Tue, 22 Sep 2009 17:16:50 GMT

Hi,

my customer requires a report that in one column shows the sales from January till current month-1,

in the second column - the sales for the current month.

Class() is not working properly in this case...

I also managed to create a calculation that does "January-May" & "Jun" (as two columns, for example), but the numbers are wrong...

Any suggestions?

Tnx

TimePeriods as Dimetions

Tue, 22 Sep 2009 21:39:55 GMT

What does your data look like?

If you have months as a dimension in your data, you could do something like:

Exp 1: Sum({<Month = {'>=1<$(=Month(Today()))'}>} Sales)
Exp 2: Sum({<Month = {'$(=Month(Today())'}>} Sales)

If you have sales data and only have the sale date, I would suggest adding month in the load script. You could do something similar to the above using straight dates, but it's a bit more complicated.

TimePeriods as Dimetions

xtrimf — Tue, 22 Sep 2009 22:31:50 GMT

hi,

I can't use expressions because I'm already using them as dimentions to populate the table.

I need a calculated dimention.

And Yes, I am using "Month".

TimePeriods as Dimetions

xtrimf — Wed, 23 Sep 2009 13:53:11 GMT

TimePeriods as Dimetions

xtrimf — Wed, 23 Sep 2009 15:26:27 GMT

attached is an illustration of what i'm trying to achieve

TimePeriods as Dimetions

Thu, 24 Sep 2009 01:51:05 GMT

Calculated dimensions can be a little tricky. You can't seem to use Aggreagation functions inside, trying to calculate your date span in the calculated dimension is out.

I did a Resident Load on the data and pulled out the min and max of the span. Then I built a calculated dimension to either be the current month or anything else, which then displays the span. The calculated dimension is messy, due to trying to pull out the month names using the number. Essentially, it is:

if(Month = Month(Today()), Month, SpanName)

I've attached a sample app that contains the resident load and the calculated dimension, along with a pivot table resembling your example.

TimePeriods as Dimetions

xtrimf — Thu, 24 Sep 2009 17:14:39 GMT

Its all good...but the dimention should be dynamic and not static....

TimePeriods as Dimetions

prieper — Thu, 24 Sep 2009 18:29:40 GMT

Hi,

it should be possible to solve it also by SET-functions, but am not that firm. You may determine your timespan by some variable, which then should be hidden somewhere is your application.

See the attached example

HTH
Peter

TimePeriods as Dimetions

xtrimf — Thu, 24 Sep 2009 19:22:53 GMT

Tnx 🙂

Works great...i did something similar but your is more accurate 🙂

tnx again