topic Re: help with expression - aggregation DIFFERENT MONTHS in QlikView

help with expression - aggregation DIFFERENT MONTHS

silvia_assuncao — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

Please could someone help me with this expression problem.

the aggregation was working perfectly, until I added the months as dimensions, and I'm not getting to rebuild the expression so that I'll have the right value each month.

total values by month should be as the first chart.

find the file attached here

Re: help with expression - aggregation DIFFERENT MONTHS

sunny_talwar — Wed, 13 Jul 2016 23:33:42 GMT

May be one of the two options

Expression 1:

RangeSum(

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'-'}>} [Adjust Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'+'}>} [Adjust Amount]/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Discount Type] = {'C'}, [Discount Signal] = {'-'}>} [Discount Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Plan Type] = {'C'}, [Registry ID] = {'60'}>} [Plan Amount with Taxes]/100))

If(Dimensionality() = 2, Count(TOTAL <[Unique Account ID], [Reference Month]> DISTINCT [Unique Resource ID]), 1)

Expression2:

RangeSum(

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'-'}>} [Adjust Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'+'}>} [Adjust Amount]/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Discount Type] = {'C'}, [Discount Signal] = {'-'}>} [Discount Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Plan Type] = {'C'}, [Registry ID] = {'60'}>} [Plan Amount with Taxes]/100))

If(Dimensionality() = 2, Count(TOTAL <[Unique Account ID], [Reference Month]> DISTINCT {<[Unique Resource ID] = {"=Len(Trim([Unique Resource ID])) > 0"}>} [Unique Resource ID]), 1)

First one includes the null value in the count when finding the Unique Resource ID level amount, where as the second one exclude the null.

Re: help with expression - aggregation DIFFERENT MONTHS

silvia_assuncao — Thu, 14 Jul 2016 16:45:33 GMT

The second one is the one that I need

but I need the lines in which the [Unique Resouce ID]is not a numberic format to desapear. what can be done in the expression?

Re: help with expression - aggregation DIFFERENT MONTHS

silvia_assuncao — Thu, 14 Jul 2016 16:46:15 GMT

The second one is the one that I need

but I need the lines in which the [Unique Resouce ID]is not a numberic format to desapear. what can be done in the expression?

Re: help with expression - aggregation DIFFERENT MONTHS

sunny_talwar — Thu, 14 Jul 2016 16:49:00 GMT

Try selection 'Suppress When Value Is Null' for your second dimensions

Re: help with expression - aggregation DIFFERENT MONTHS

silvia_assuncao — Thu, 14 Jul 2016 16:55:18 GMT

it worked for the first item, but the second still remains.

The one below is the one which still remains:

Re: help with expression - aggregation DIFFERENT MONTHS

sunny_talwar — Thu, 14 Jul 2016 16:59:12 GMT

Try one more time now

Re: help with expression - aggregation DIFFERENT MONTHS

silvia_assuncao — Thu, 14 Jul 2016 17:04:38 GMT

Great!!! You saved the day again!!

thanks

Re: help with expression - aggregation DIFFERENT MONTHS

sunny_talwar — Thu, 14 Jul 2016 17:18:31 GMT

No problem at all

Re: help with expression - aggregation DIFFERENT MONTHS

silvia_assuncao — Mon, 18 Jul 2016 19:48:16 GMT

Hi again!

Now I'm creating a new chart with the same values, but I've changed the dimention to COST CENTER, but I was not able to get how to change the aggr and have the right values within the lines.

could you please help me again?

Re: help with expression - aggregation DIFFERENT MONTHS

sunny_talwar — Mon, 18 Jul 2016 23:28:08 GMT

Can you check if this is what is needed:

Avg({<[Unique Resource ID] = {"=Len(Trim([Unique Resource ID])) > 0"}>} 1) *

RangeSum(

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'-'}>} [Adjust Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Adjust Type]= {'C'}, [Registry ID] = {'70'}, [Adjust Signal] = {'+'}>} [Adjust Amount]/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Discount Type] = {'C'}, [Discount Signal] = {'-'}>} [Discount Amount]*(-1)/100),

Sum(TOTAL <[Unique Account ID], [Reference Month]> {<[Plan Type] = {'C'}, [Registry ID] = {'60'}>} [Plan Amount with Taxes]/100))

If(Dimensionality() = 3, Count(TOTAL <[Unique Account ID], [Reference Month]> DISTINCT {<[Unique Resource ID] = {"=Len(Trim([Unique Resource ID])) > 0"}>} [Unique Resource ID]), 1)

Re: help with expression - aggregation DIFFERENT MONTHS

silvia_assuncao — Wed, 17 Aug 2016 17:59:53 GMT

Hi Sunny, when I collapse all the information and stay only with cost center showing the values showed for cost center are not correct.

could you please help me once more?

thanks