topic Re: Problem in set analysis in QlikView

Problem in set analysis

Mon, 21 Jan 2013 18:56:58 GMT

Hello everyone thanks in advance for taking the time to check this.

I have the next expression to get the balance in wich i have to sum(Debit-Credit) but i have to do this from the first record to the last like accumulating the values, so i use this expression:

sum({$<RefDate={"<=$(=Max(RefDate))"}, Year={'*'}, Month={'*'}>} Debit-Credit)

this works fine when i have only Account as dimension like this:

The problem is when i add the month dimension it does not accumulate the values and i get this:

As you can see the totals are the same as the first table but the amount im getting its only for april so i would like to have it in april in the second table so im guessing its a problem with the set analysis i hope someone has already done something similar and it could help

Regards

Re: Problem in set analysis

Mon, 21 Jan 2013 19:39:18 GMT

If you split the expression by month in your chart table, in every column you have access only this set of data (one month). For this case you should use the chart range functions. I think a combination of RangeSum() and Above() could be your solution. Look for this functions in the QlikView help, and you will find some examples.

Regards

Re: Problem in set analysis

Mon, 21 Jan 2013 19:47:58 GMT

I've already tried to accumulate by using Before funtion but it only works for the firsts months i'll try mixing it with rangesum and see what happens

Re: Problem in set analysis

danielrozental — Mon, 21 Jan 2013 20:01:53 GMT

Alfredo, best way to solve this in my opinion is by script, either by having a field with the accumulated value or by having an accumulated dimension, something like

Type	MonthToShow	Month
Accumulated	Mar-2013	Jan-2013
Accumulated	Mar-2013	Feb-2013
Accumulated	Mar-2013	Mar-2013
Not Accumulated	Mar-2013	Mar-2013