topic Re: Adjusting formula to be generic in QlikView

Adjusting formula to be generic

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

Hello,

please find attached this table.

I have an issue please help to check.

In the table i want to calculate the cumul sales and i noticed it works by using this formula

RangeSum(Above(TOTAL ([Actual Sales]), 0, RowNo(TOTAL))) but if i want to see many sub zones more than two let's say 10 sub zones the formula does not work correctly.

So can you adjust the formula in order to get correct values even i wawnt to see many sub zones at the same time?

Just another note the formula works correctly with many sub zones for only january and february and then it works incorrectly.

Thank you for helping me.

Alâa Eddine.

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 11:12:29 GMT

What happens when you add more than 2? Can you show a screenshot with 3 and explain what the issue is?

Re: Adjusting formula to be generic

OmarBenSalem — Fri, 05 Oct 2018 11:12:46 GMT

what if u try :

aggr(

rangesum(above(

sum([Actual Sales])

,0,rowno())

,(YearMonth,(Numeric,Ascend)), SubZone)

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 11:13:54 GMT

Did you mean this Omar

aggr(

rangesum(above(

sum([Actual Sales])

,0,rowno())

,SubZone, (YearMonth,(Numeric,Ascend)))

Re: Adjusting formula to be generic

OmarBenSalem — Fri, 05 Oct 2018 11:14:59 GMT

Sure !

Re: Adjusting formula to be generic

Anonymous — Fri, 05 Oct 2018 11:30:32 GMT

Hello,

this is what it happens if choose more than two sub zones.

You see here for BBGR France the values are not the same comparing to first figure.

Thanks.

Alâa Eddine.

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 11:33:37 GMT

Can't really compare because the period selection is different within the two screenshots

Re: Adjusting formula to be generic

Anonymous — Fri, 05 Oct 2018 12:12:26 GMT

Hello,

i applied the formula and i have an issue:

Thank you for your help.

Alâa Eddine.

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 12:21:10 GMT

Are you using QlikView 12 or above? If you are, then ignore the error and click okay to see what you get

Re: Adjusting formula to be generic

Anonymous — Fri, 05 Oct 2018 12:24:32 GMT

Hello,

thank you i have QlikView 11.

Alâa Eddine.

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 12:31:31 GMT

Then the above expression won't work. But going back to your screenshot.... can you take a comparable period screenshot to show the issue?

Re: Adjusting formula to be generic

Anonymous — Fri, 05 Oct 2018 12:35:06 GMT

Sorry but what did you mean exaclty?

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 12:37:49 GMT

Look at the screenshot here

Your first screenshot is showing from Year-Month 2018-01 to 2018-09 and the second one is from 2017-03 to 2017-11. See how the years are different between the two screenshots. Does this make sense?

Re: Adjusting formula to be generic

Anonymous — Fri, 05 Oct 2018 12:38:11 GMT

Hello,

do you mean that i have to keep the same periods?

if yes this is the screenshot;

Re: Adjusting formula to be generic

Anonymous — Fri, 05 Oct 2018 12:39:17 GMT

Yes i understood, sorry i just sent to you the new screenshot with the correct period.

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 12:45:58 GMT

So, the only value that doesn't match in the screenshots provided is the value for 2018-09, right? Would it be possible for you to share your app to see what might be going on?

Re: Adjusting formula to be generic

Anonymous — Fri, 05 Oct 2018 13:11:44 GMT

Hello,

no, it is not always the case sometimes all values will be incorrect.

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 13:29:15 GMT

Okay would you be able to share a sample to check this out?

Re: Adjusting formula to be generic

Anonymous — Fri, 05 Oct 2018 15:09:02 GMT

Hello,

i dot not know how and i do not if i have the right to share with you.

Thanks.

Alâa Eddine.

Re: Adjusting formula to be generic

sunny_talwar — Fri, 05 Oct 2018 15:10:42 GMT

Well, I can't ask you to do what you are not sure about, but see if these link help

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample