Voltage multiplier does not work
The 1 nF capacitor has an impedance of 3.18 MΩ at 50 Hz and you are connecting this to your oscilloscope probe that probably has an input resistance of between 1 MΩ and 10 MΩ. Do you see the problem here?
The BY228GP diode has a specified leakage of typically 5 uA. That's barely below the current flow into the capacitor at 50 Hz from 24 volts so, do you see this problem too?

I tried connecting a 100KHz square wave @ 50% duty cycle and 5V peak
voltage. so at this frequency, the impedance of the caps would be
1.6K, conciedarably lower than the measuring instrument (1-10 MOhm), but I am still not getting my output correctly

Now your problem is the forward volt-drop of the diode. If we consider it to be circa 1 volt, then, because there are two diode drops per stage, the 5 volt source is eroded by 2 volts. This is equivalent to a 3 volt driving source using ideal diodes. Does that tally with what you found?