Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.
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
oh, ich probiere gleich danke!. ja in berlin 😉 small world