topic Re: Rangesum with two Dimensions in QlikView

Rangesum with two Dimensions

Sun, 23 Mar 2014 22:44:37 GMT

Hi.

I´m having a problem with a Rangesum expression and need your help.

I´ve got a pivot table with two Dimensions, Month and Day. Then I´ve got in the expressions my Sales , Sales LAst year, and then my problem appears... I need to have a YTD, that gives me accumulated values for Month and Day when I expand my Day dimension is expanded. That´s when I cannot see what I wish. My Values for YTD Month are fine, and for Accumulated Day granularity are also fine, but with January expanded (or another Month), the other Months do not accumulate, giving me just the Sales for each Month by it self.

I´m using this expression:

=RangeSum(Alt(Above(TOTAL [myexpressionName]), 0), rangesum(above(sum({$<Year={2013}>} (A - B - C)),0)))

My Sales expression is a Calculated sum of A - B - C

Reminding that My Values are fine when Day is not expanded;

My Accumulated Days Values are also fine;

Only my Months that are NOT expanded, that doesn´t Accumulate, giving me just each Month Total.

I´ve tried to Start the expression with Sum(Aggr(...)), but that mess with my values and does not give me what I need.

Thanks in Advance!

Best Regards,

Bruno Silva.

Re: Rangesum with two Dimensions

jolivares — Mon, 24 Mar 2014 01:08:08 GMT

Try this two expressions:

Sales: Sum(Sales)

Acc_Sales:

If(Dimensionality()=0, [Sales],

If(Dimensionality()=1,

Sum(Aggr(RangeSum(Above(TOTAL Sum(Aggr(Sum(ValNetoUSD),Day)),0,RowNo(TOTAL))),Month))

RangeSum(Above(TOTAL [Sales],0,RowNo(TOTAL)))

)

See that you need to use dimensionality function to do this. If this not work, try to post a qvw with some data,

Re: Rangesum with two Dimensions

Mon, 24 Mar 2014 10:10:20 GMT

Good Morning Juan.

I tried what you suggested, but it´s not working very well.

When looking at Day Dimension, it´s fine, but at Month Dimension, the values are all wrong. I´ve tried some things, but cannot solve it!

Here is an example of the pivot table:

Thanks!

Bruno Silva.

Re: Re: Rangesum with two Dimensions

jolivares — Mon, 24 Mar 2014 12:37:57 GMT

Check this...

Re: Rangesum with two Dimensions

Mon, 24 Mar 2014 13:36:13 GMT

Hi Juan.

I appreciate your help, but my problem persists...

The problem is that my client wants to be able to view accumulated Month, even with one Day dimension expanded in a Month.

Right now what happens is that when you expand Day Dimension in a Month, ALL other Months appears as 0. Without values. Imagine that you have 250 as total for January, and 150 for February.

Jan |Day |Sales |AccSales

|1 |100 | 100

|10 |100 | 200

|15 |50 | 250

Feb | 150 | 400 (This Month has Day Dimension Collapsed)

Right now what you see when Day Dimension is Expanded in February is Feb |150 |0.

I think that this clarifies what my client wants to see.

Best Regards,

Bruno Silva.

Re: Re: Rangesum with two Dimensions

jolivares — Mon, 24 Mar 2014 18:15:37 GMT

See the attached example, I export and use your data.

Re: Rangesum with two Dimensions

Mon, 24 Mar 2014 23:16:44 GMT

Hi Juan.

It works fine in your example, but not in my data. I´m missing something...

Re: Rangesum with two Dimensions

Tue, 25 Mar 2014 10:13:55 GMT

Good Morning Juan.

I found out the problem that makes your example incompatible with my data...

When you exported the data to Excel, you have deleted 2012 data, therefore it remains only 2013 in the file, and like that your example did work, but in my data with 2012 and possible other Years, it doesn´t work.

Any suggestions to solve this? I´ve tried a million possibilities, and nothing solve it!

Best Regards,

BS.

Re: Re: Rangesum with two Dimensions

jolivares — Tue, 25 Mar 2014 12:24:38 GMT

Now you have two years...

Re: Rangesum with two Dimensions

Wed, 26 Mar 2014 16:15:31 GMT

Thank you very much for your help. Your example is working just fine!

Just tell me something... How did you aggregate the data to date? When I first send you my sample Qvw, the data wasn´t aggregated. Did you use "Group by" in script with Sum(Values) as Sales (...) Group by Date? Or did you done that in Excel when you exported the data?

Thanks again for your help and patience!

Best Regards,

Bs.

Re: Rangesum with two Dimensions

jolivares — Wed, 26 Mar 2014 16:21:20 GMT

I just simple export your data to excel and re-import to QV, that's it!!!

I really do that to assure that the data is correct and don't have a missing ones.

Re: Rangesum with two Dimensions

Wed, 26 Mar 2014 17:55:00 GMT

In fact, your example works just fine (That´s why I marked it as Correct Answer), however when deploy it to my data, the first two rows are messed up... First Row (Jan) shows Jan and Feb sum, the second row (Feb) shows just Feb, after that it´s all fine. This continues even when Day Dimension is expanded. All days are fine, but sub-totals(with January expanded) show Jan + Feb, then Feb (witch is collapsed) stays showing just Feb, after that all values are fine!

Re: Rangesum with two Dimensions

jolivares — Wed, 26 Mar 2014 18:51:36 GMT

I think that your data has some trouble, try to purge it.

Maybe you can export your data to an excel file or a txt and reload. See what happens.

Where that data come from? Try to post raw data in order I can see it.

Re: Rangesum with two Dimensions

Thu, 27 Mar 2014 10:48:31 GMT

Hi Juan.

Here is the Excel File, with non aggregated Dummy Data as it comes from client (I cannot send original Data, so I put "1" in every field). I don´t think that there is any problem with the data. It´s just a Date, and 3 fields, where the calculations will be A-B-C in this case.

I´ve tried to aggregate it in Script, doing "Sum(A-B-C) as Sales (...) group by Date", and also tried in formula, (A-B-C), substituting "Sales". Always shows it as I said it above. Wrong values in Jan, and Feb...

Dummy Data: DummyData.xlsx

Best Regards,

BS.

Re: Rangesum with two Dimensions

Thu, 27 Mar 2014 12:44:15 GMT

Just Solved it!

You were right! It was a misplaced value that was messing the data! As I put it in the right date order, the error was gone... Finally!

Thank you very much for your help, time and patience!

Best Regards,

BS.