Is there a way in Qlikview to calculate the attached values?

[#1]

Hi,

I´ve no idea how to calculate the attached values (s. excel) in the attached qlikview file in order to show them in a graph. It would be great if someone could help me on that.

Thanks in advance!
David

[#2]

Hi,

please give us the formulas that calculate the red values.

Would be a great help, even if you only use the sample values from the example.

Greets

Klaus

[#3]

sure, for the first colum (monthsSinceFirstOrder =0) I simply divided the value from cell I2/$C2

For the rest of the columns (monthsSinceFirstOrder 1 to 9) I divided the value from the respective month and added the value from the previous month like:

Example:

for monthsSinceFirstOrder = 1 the formula is J2/C2 +  C15

for monthsSinceFirstOrder = 2  the formula is K2/C2 + D15

for monthsSinceFirstorder = 3 , L2/C2+E15

and so on...

you can also see the formulas in the attached excel.

Thanks!
David

[#4]

Thanks,

I could have examined the Sheet a bit closer 😞

Just join that 2 tables on firstY and firstM and define expressions in the chart.

With attribut names 'M0' for column I, M1 for column J and so onyou get

monthsSinceFirstorder = 2 with (M0+M1+M2)/sum(Erstbestellung)

monthsSinceFirstorder = 3 with (M0+M1+M2+M3)/sum(Erstbestellung)

Where do you get sum(Erstbestellung) from? is it already in a table or do you have to calculate it?

Greets

Klaus

[#5]

sum (erstbestellung) is already in a table. see attached qlikview file, first table.

Not sure I know how to do the join exactly. could you do it and send the qlikview file back? that would help a lot.

[#6]

Sorry, I'm on personal edition at the moment

but this - modulo a few syntax modifications - will do the trick:

Fact:

LOAD firstY, firstM, sum_Erstbestellung

RESIDENT Erstbestellung

JOIN

LOAD firstY, firstM, M0 as E0, M0+M1 as E1, M0+M1+M2 as E2, ...

RESIDENT Bestellungen

In the chart define expressions E0/sum_Erstbestellung, E1/sum_Erstbestellung, E2/sum_Erstbestellung

BTW auch in/aus Deutschland? Erstbestellung 😉

Grüße

Klaus

[#7]

oh, ich probiere gleich danke!. ja in berlin 😉 small world