topic Re: cumulative calculation in QlikView

cumulative calculation

kumarkp412 — Wed, 18 Mar 2015 04:31:56 GMT

Hi friends ,

I have struck in creating the pivot table.

I want to calculate the values in the expression as (subtotal - current cell ).

for example : I am having two dates (start date and end date) as dimension and one expression sales as fact.

By creating the pivot table , i am using the start date and end date as dimension and in the expression as sales.

In the expression , i want to show the value as subtotal of the row - current cell value for that i am using sum(total sales) - sum(sales).But it is given the value as , total sales of the current sales.i am not able to find the solution

kindly grab some time to solve this.

Thanks in advance

Kumar

Re: cumulative calculation

jyothish8807 — Wed, 18 Mar 2015 05:11:39 GMT

Hi Parveen,

Over here the issue is because of start date and end date. Both are in dates but if Start date is Jan the End date may be Feb. Because of this the mismatch is happening.

So take only start date as dimension, and try.

Regards

Re: cumulative calculation

kumarkp412 — Wed, 18 Mar 2015 05:40:50 GMT

Hi jyothish,

Thanks for reply ,

But i want to show the two dates in the dimension and take the value for that,

Thanks

Kumar

Re: cumulative calculation

Wed, 18 Mar 2015 07:08:03 GMT

Hi,

You can use the set analysis in expression.

Re: cumulative calculation

jyothish8807 — Wed, 18 Mar 2015 07:27:05 GMT

Hi Praveen

You can try something like this:

create a expression and give a label "new" then use this expression: amendment might require

sum(total <stdt>sales)-RangeSum(Alt(Before(new),0),Sum(sales))

RangeSum(Alt(Before(new),0),Sum(sales)) will give you the cumulative sum.

Hope it helps

Regards

Re: cumulative calculation

kumarkp412 — Wed, 18 Mar 2015 07:28:30 GMT

Hi Krishnama ,

In this how to use the set analysis ,.can you give me one example please

Thanks

Kumar

Re: cumulative calculation

hariprasadqv — Wed, 18 Mar 2015 07:40:39 GMT

Hi Praveen,

you can use set analysis In this kind. Find the below URLs they mya help you.

Set analysis using date range?

Set Analysis for Prior Periods Cumulative Sum with Non Contiguous Period Selections

QlikView App: Set Analysis - Prior Period Comparison

Re: cumulative calculation

kumarkp412 — Wed, 18 Mar 2015 09:57:23 GMT

Hi Jyothish,

but it is giving the different value , what i required.

thanks

Kumar