topic Re: Expression by Dimension in QlikView

Expression by Dimension

Thu, 13 Aug 2015 20:23:36 GMT

I wanna display % Growth of Sale as follow the formula : (the current - last month ) / last month * 100 . Dimension is Quarter, Month, Week. Parameter is a store. Like a picture.

I try this expresion for a week but it doesnt work : If(WeekYear='29', (Sum(O_TriGiaBan) - Sum({<WeekYear={'28'}>} O_TriGiaBan))/ Sum({<WeekYear={'28'}>} O_TriGiaBan) * 100)

.I wanna calc all the week in the dimension. Please help me!

Re: Expression by Dimension

Qrishna — Thu, 13 Aug 2015 21:08:16 GMT

provide some sample date.

Re: Expression by Dimension

Fri, 14 Aug 2015 02:19:10 GMT

I will give you my Qlikview file and you can review detail of file !

Re: Expression by Dimension

sunny_talwar — Fri, 14 Aug 2015 02:55:11 GMT

Working with a Personal Edition of QlikView, and hence prepared my own sample to try and show how this can work. You may need to alter this based on your need and requirement.

Script:

Table:

LOAD *,

(Rand() * 100000) as Value,

'A' as Product,

Week(Date) as Week,

Month(Date) as Month,

Year(Date) as Year,

Ceil(Month(Date)/3) as Quarter;

LOAD Date((Today() + 365) - RecNo()) as Date

AutoGenerate 730;

Concatenate(Table)

LOAD *,

(Rand() * 100000) as Value,

'B' as Product,

Week(Date) as Week,

Month(Date) as Month,

Year(Date) as Year,

Ceil(Month(Date)/3) as Quarter;

LOAD Date((Today() + 365) - RecNo()) as Date

AutoGenerate 730;

Created a Pivot Table with the following information:

Dimensions: Product, Year, Quarter, Month, Week

Expressions:

1) Current Value: =Sum(Value)

2) Previous Value: =If(Above(TOTAL Product) = Product, Above(TOTAL Sum(Value)))

3) % Change: =Sum(Value)/If(Above(TOTAL Product) = Product, Above(TOTAL Sum(Value))) - 1

Screenshot:

Attaching the qvw for your reference as well.

HTH

Best,

Sunny

Re: Expression by Dimension

Fri, 14 Aug 2015 04:07:06 GMT

it 's work. But I check Show Partial Sum in Presentation Tab of Chart Properties. And it display the Total Sum (value ) but % Change dont display and the Previous Value dont display, too . The following picture :

I wanna display all the total % Change and Previous Value. Please help me again

Re: Expression by Dimension

sunny_talwar — Fri, 14 Aug 2015 04:18:06 GMT

What partial total would you be wanting to see for the Previous Column???

Re: Expression by Dimension

Fri, 14 Aug 2015 04:40:26 GMT

I wanna see Partial total of Previous Value Quarter, or Month.

For example: the Row of Total of Year 2015, the Previous Value will display the Total of Year 2014 , and % Change of 2015 compare with 2014. and the Quarter is same Year...

Re: Expression by Dimension

sunny_talwar — Fri, 14 Aug 2015 05:09:40 GMT

On it

Re: Expression by Dimension

Fri, 14 Aug 2015 05:50:28 GMT

i am so sorry about this inconvenience. I cant see it ??? Can you show me clearly ?

Re: Expression by Dimension

sunny_talwar — Fri, 14 Aug 2015 05:58:53 GMT

Still working on it

Sorry, I haven't done this before and that is why it is taking some time. Will revert as soon as I have a solution for you.

Best,

Sunny

Re: Expression by Dimension

Fri, 14 Aug 2015 07:29:55 GMT

yup , thank you so much

Re: Expression by Dimension

sunny_talwar — Fri, 14 Aug 2015 10:27:20 GMT

New Script:

Table:

LOAD *,

(Rand() * 100000) as Value,

'A' as Product,

Week(Date) as Week,

Month(Date) as Month,

Year(Date) as Year,

Ceil(Month(Date)/3) as Quarter,

Num#(Year(Date)&Ceil(Month(Date)/3)) as YearQuarter;

LOAD Date((YearStart(Today()) + 730) - RecNo()) as Date

AutoGenerate 1460;

Concatenate(Table)

LOAD *,

(Rand() * 100000) as Value,

'B' as Product,

Week(Date) as Week,

Month(Date) as Month,

Year(Date) as Year,

Ceil(Month(Date)/3) as Quarter,

Num#(Year(Date)&Ceil(Month(Date)/3)) as YearQuarter;

LOAD Date((YearStart(Today()) + 730) - RecNo()) as Date

AutoGenerate 1460;

Pivot Table with following:

Dimension: Product, Year, Quarter, Month

Expressions:

1) Current Value: =Sum(Value)

2) Previous Value:

=If(Dimensionality() = 2,

Aggr(Below(Sum(Value)), Product, Year),

If(Dimensionality() = 3,

Aggr(Below(Sum(Value)), Product, YearQuarter),

If(Above(TOTAL Product) = Product, Above(TOTAL Sum(Value)))))

3) % Change:

=If(Dimensionality() = 2,

Sum(Value)/Aggr(Below(Sum(Value)), Product, Year) - 1,

If(Dimensionality() = 3,

Sum(Value)/Aggr(Below(Sum(Value)), Product, YearQuarter) - 1,

Sum(Value)/If(Above(TOTAL Product) = Product, Above(TOTAL Sum(Value))) - 1))

Screenshot:

Now the Previous Value expression and % Change expression are using Below() instead of Above() function and I have no idea why is that the case. When I try it with Above, I don't get the results I am seeking, but when I use Below it gives me exactly what I am looking for. So you might have to see if Above or Below works for you. Because from logic I would have though Above to work.

I think it would be a good idea to involve some experts here to get better idea of why is this happening, plus they might be able to offer a much better solution also.

I request swuehl‌ MarcoWedel‌ MRKachhiaIMP‌ hic‌ rwunderlich‌ jagan‌ maxgro‌ Marcus_Sommer‌ to help us in understanding why Below is working here and not Above.

Thanks,

Sunny

Re: Expression by Dimension

swuehl — Fri, 14 Aug 2015 11:01:42 GMT

You are creating dates, year and month in descending load order

LOAD Date((Today() + 365) - RecNo()) as Date

AutoGenerate 730;

And advanced aggregation uses load order to sort dimension values. That's why you need below() inside the aggr() function.

Re: Expression by Dimension

sunny_talwar — Fri, 14 Aug 2015 11:06:20 GMT

Super Stefan. I totally forgot that you mentioned this before also. I guess, I won't make this mistake one more time now

Thank you for your assistance.

Best,

Sunny

Re: Expression by Dimension

Wed, 19 Aug 2015 10:43:17 GMT

Hi sunindia,

Now, I wanna add 1 expression next to % Change. This expression is % Change compare with last year.

Ex: in the row week 28 in the 2015, the column % Change with Prior Year will display % change of week 28 in the 2015 compare with the week 28 in the 2014 like : (value of week 28-2015) / (value of week-2014) - 1.

I have an idea, Can i make the total of quarter,month,week in Edit Script ? or Could you please give me another expresion, Sunny ?